Add - in numbers

  • Thread starter Thread starter Dig
  • Start date Start date
D

Dig

I have a column of numbers that are zip codes. Some of the codes are 9
numbers but they are without the hyphen. Example is 523521191. I want to
convert it to 52352-1191

Is there an easy way to do this in Excel XP?

Thanks
 
You can use a simple SUBSTITUTE formula. E.g.,

=SUBSTITUTE(A1,"-","")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Chip

That's the wrong way round!! Dig, try this:

==IF(ISERROR(FIND("-",E8)),LEFT(E8,5)&"-"&RIGHT(E8,4),E8)

Andy.
 
Alan.

NICE answer!!! I like that one.

Andy.

Alan said:
Format cells as Custom [<=99999]00000;00000-0000


Dig said:
I have a column of numbers that are zip codes. Some of the codes are 9
numbers but they are without the hyphen. Example is 523521191. I want to
convert it to 52352-1191

Is there an easy way to do this in Excel XP?

Thanks
Click to expand...
Click to expand...
 
I find that the easiest way is just to set a special format to the
cells

Click >cells>number tab>special and you can select zip+4 phone number
SS# formats which are applied automatically
 
Cant take any credit, its in Help! Found it last week,
Alan.
Andy B said:
Alan.

NICE answer!!! I like that one.

Andy.

Alan said:
Format cells as Custom [<=99999]00000;00000-0000


Dig said:
I have a column of numbers that are zip codes. Some of the codes are 9
numbers but they are without the hyphen. Example is 523521191. I want to
convert it to 52352-1191

Is there an easy way to do this in Excel XP?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Worked Great Thanks

Alan said:
Format cells as Custom [<=99999]00000;00000-0000


Dig said:
I have a column of numbers that are zip codes. Some of the codes are 9
numbers but they are without the hyphen. Example is 523521191. I want to
convert it to 52352-1191

Is there an easy way to do this in Excel XP?

Thanks
Click to expand...
Click to expand...
 
Thanks


Randall Roberts said:
I find that the easiest way is just to set a special format to the
cells

Click >cells>number tab>special and you can select zip+4 phone number
SS# formats which are applied automatically
Click to expand...
 
Back
Top