Inserting Characters

R

rae820

So I have a column of zip codes - some with 5 numbers and some with 9.
What I am trying to do is create a formula where if there are more tha
5 characters in that cell, insert a "-" after the 5th character from th
left...does anyone know how to do that?

what I am trying to do...
before:
503122540

after:
50312-2540

without going through each cell manually!

Thank
 
E

Excelenator

You could use this formula in an adjacent cell and copy it down th
column, then copy the results and paste special values over th
original data (if you want to replace the data ONLY) then remove th
calculated column.

=IF(LEN(A1)>5,MID(A1,1,5) & "-" & MID(A1,6,50),A1
 
R

Ron Rosenfeld

So I have a column of zip codes - some with 5 numbers and some with 9.
What I am trying to do is create a formula where if there are more than
5 characters in that cell, insert a "-" after the 5th character from the
left...does anyone know how to do that?

what I am trying to do...
before:
503122540

after:
50312-2540

without going through each cell manually!

Thanks
Click to expand...

=TEXT(A1,"[>99999]00000-0000;00000")

will do what you describe and also retain any leading zeros.
--ron
 
R

Ron Rosenfeld

You could use this formula in an adjacent cell and copy it down the
column, then copy the results and paste special values over the
original data (if you want to replace the data ONLY) then remove the
calculated column.

=IF(LEN(A1)>5,MID(A1,1,5) & "-" & MID(A1,6,50),A1)
Click to expand...


Of course, if the zip code has a leading zero, as mine does, your formula does
not give useful results:

046670208

Your formula gives: 46670-208 when a more useful result would be 04667-0208

See my response for a different solution.
--ron
 
Top