Inserting Characters
 

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...
befo
503122540

after:
50312-2540

without going through each cell manually!

Thanks


--
rae820
------------------------------------------------------------------------
rae820's Profile: http://www.excelforum.com/member.php...o&userid=24135
View this thread: http://www.excelforum.com/showthread...hreadid=573110

Inserting Characters
 

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)


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=573110

Inserting Characters
 

On Fri, 18 Aug 2006 11:16:29 -0400, rae820
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...
befo
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

Inserting Characters
 

On Fri, 18 Aug 2006 11:56:49 -0400, Excelenator
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