Insert Hyphens in Column of Text-Formatted Numbers
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Insert Hyphens in Column of Text-Formatted Numbers

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I imported columns of data into a blank spreadsheet, one of which was a column of numbers, all 10 digits in length, and formatted as text (intentionally). I now need to insert hyphens in two places in all the numbers (e.g. changing the appearance from 1234567890 to 1234-56-7890), and although I know I could do this manually, I have several hundred unique records in the column and have to believe Excel has a more automated way to do this. Any suggestions would be much appreciated. Thanks in advance for any help!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use the formula...

    =REPLACE(REPLACE(A2,5,0,"-"),8,0,"-")

    ...to insert hyphens in the text value in cell A2.

    [ This Message was edited by: Mark W. on 2002-04-08 08:26 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Am assuming that all of your numbers are of the same digit length? 10?

    Ex:
    All of your numbers are in column A
    Place this formula in Column B and fill it down.

    =LEFT(A1,4) & "-" & MID(A1,5,3) & "-" & RIGHT(A1,3)

    Tom

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-08 08:14, gonnabe wrote:
    I imported columns of data into a blank spreadsheet, one of which was a column of numbers, all 10 digits in length, and formatted as text (intentionally). I now need to insert hyphens in two places in all the numbers (e.g. changing the appearance from 1234567890 to 1234-56-7890), and although I know I could do this manually, I have several hundred unique records in the column and have to believe Excel has a more automated way to do this. Any suggestions would be much appreciated. Thanks in advance for any help!

    Try

    =TEXT(A1,"0000-00-0000")

    Regards,

    Peo Sjoblom

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for all the suggestions. I'll summarize my success (or lack thereof) with each.

    Mark---I tried the Replace command as you'd suggested, and after some frustration with the formula showing up instead of the revised text, I finally managed to get it to work.

    Tom---Correct. All cells in the column had 10 digits each. This option worked as well, but I had to tweak the formula slightly to insert the second hyphen in the proper position so it followed the xxxx-xx-xxxx format. Ended up using =LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & RIGHT(A1,4)

    Mark (again)---This one seemed to be the most straightforward and easiest. (Guess I should've paid closer attention during that Excel training session!) I replaced the zeroes with question marks, copied the formula down through the rest of the column, and there everything was, hyphens and all.

    One last question---for each option, the reformatted text with hyphens HAS to be in a distinct column from the original data, correct? Just wanted to confirm, as I can hide the original column and only show the good, hyphenated numbers.

    Thanks once again to everyone for their help!

    John ("gonnabe" an Excel wiz someday)

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Whoops! Sorry, Peo! I should've given you credit for that last suggestion of using =TEXT(A1,"0000-00-0000")---which I tweaked to =TEXT(A1,"????-??-????")---to you. I misread the string the first time around.

    Thanks again for the help!

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One last question---for each option, the reformatted text with hyphens HAS to be in a distinct column from the original data, correct? Just wanted to confirm, as I can hide the original column and only show the good, hyphenated numbers.

    Thanks once again to everyone for their help!

    John ("gonnabe" an Excel wiz someday)
    John, you can Copy the column containing any of these formulas and replace 'em with their resultant values using Paste Special Values... Once done you can eliminate the original column.

  8. #8
    New Member
    Join Date
    Sep 2011
    Location
    Vanderbijlpark, South Africa
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Insert Hyphens in Column of Text-Formatted Numbers

      
    Quote Originally Posted by gonnabe View Post
    Thanks for all the suggestions. I'll summarize my success (or lack thereof) with each.

    Mark---I tried the Replace command as you'd suggested, and after some frustration with the formula showing up instead of the revised text, I finally managed to get it to work.

    Tom---Correct. All cells in the column had 10 digits each. This option worked as well, but I had to tweak the formula slightly to insert the second hyphen in the proper position so it followed the xxxx-xx-xxxx format. Ended up using =LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & RIGHT(A1,4)

    Mark (again)---This one seemed to be the most straightforward and easiest. (Guess I should've paid closer attention during that Excel training session!) I replaced the zeroes with question marks, copied the formula down through the rest of the column, and there everything was, hyphens and all.

    One last question---for each option, the reformatted text with hyphens HAS to be in a distinct column from the original data, correct? Just wanted to confirm, as I can hide the original column and only show the good, hyphenated numbers.

    Thanks once again to everyone for their help!

    John ("gonnabe" an Excel wiz someday)

    Thanks guys, you saved me a lot of time
    I have letter and numbers in my part no and the formula that work the best was =LEFT(A1,4) & "-" & MID(A1,5,2) & "-" & RIGHT(A1,4), modified it slightly but its working like a charm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com