Inserting Characters

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

[Excelenator]

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)

[Ron Rosenfeld]

On Fri, 18 Aug 2006 11:16:29 -0400, rae820
<[email protected]> wrote:

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

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

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

[Ron Rosenfeld]

On Fri, 18 Aug 2006 11:56:49 -0400, Excelenator
<[email protected]> wrote:

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