Inserting Characters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rae820 - 18 Aug 2006 16:16 GMT

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

Reply to this Message

Excelenator - 18 Aug 2006 16:56 GMT

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

--
Excelenato

Reply to this Message

Ron Rosenfeld - 18 Aug 2006 17:17 GMT

>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)

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

Reply to this Message

Ron Rosenfeld - 18 Aug 2006 17:14 GMT

>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
[quoted text clipped - 11 lines]
>
>Thanks

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

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

Reply to this Message