Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges - what characters are (or are not) allowed in the nam | Excel Discussion (Misc queries) | |||
How can I control the length of characters a code will return? | Excel Discussion (Misc queries) | |||
Inserting characters into text already typed | Excel Discussion (Misc queries) | |||
Formula to replace invalid filename characters | Excel Worksheet Functions | |||
inserting unicode characters | Excel Discussion (Misc queries) |