Insert Hyphens in Column of Text-Formatted Numbers

[gonnabe]

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!

[Mark W.]

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 ]

[Tom Schreiner]

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

[Peo08]

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

[gonnabe]

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)

[gonnabe]

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!

[Mark W.]

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.

[RSLIPPERS]

Originally Posted by gonnabe

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