Insert Hyphens in Column of Text-Formatted Numbers

gonnabe

New Member
Joined
Apr 7, 2002
Messages
7
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top