Results 1 to 6 of 6

Thread: Formula or Macro to Move last 6 Digits

  1. #1

    Formula or Macro to Move last 6 Digits



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    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.


  3. #3
    Thank you very much NBVC, the formula worked. I really appreciate your help

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You're welcome.


  5. #5
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    in VBA:

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

  6. #6
    Thanks to you too snb for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •