Formula or Macro to Move last 6 Digits

chrisjack

New member
Joined
Oct 22, 2012
Messages
15
Reaction score
0
Points
0
I have a spreadsheet with 2 columns A and B. In column B I want to move the last 6 digits of the part # to the right and add parentheses around those last 6 digits as I have done maually up to row 40. I have over a thousand parts and I'm trying to find a formula or macro that could be used to accomplish this goal in a timely manner. Attached a a sample of the spreadsheet. Thanks in advance for your help.

Christian
 

Attachments

  • Danee_WorkFile_110713.xlsm
    16.3 KB · Views: 15

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You can put this formula in C2:

=REPLACE(B2,LEN(B2)-5,0," (")&")"

copied down.

Then you can copy column C and Paste Special >> Values over column B. Then finally delete column C.
 

chrisjack

New member
Joined
Oct 22, 2012
Messages
15
Reaction score
0
Points
0
Thank you very much NBVC, the formula worked. I really appreciate your help
 

snb

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
in VBA:

Code:
Sub M_snb()
    [C2:C20000] = [if(B2:B20000="","","'(" & right(B2:B20000,6) & ")")]
End Sub
 
Top