Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormattingLost
I have a column that has the zip codes in the cells. Some are 5 digit
and some are 9 digit, without the - in them. They were a number stored as text and I changed them to a number. I formatted them using the format selection from the popup menu. I used special|zip code + 4. When I use a formula to put the address, city, state and zip together in another column the - is missing from the zip code (546789087). Also the 5 digit codes are not formatted correctly. Is there a formula that will check the code to be 5 or 9 and add the - to 9 digit code? What would cause the formatted code not to copy? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormattingLost
"were a number stored as text and I changed them to a number"
formatting is only what you see, not necessarily what is in the cell. You change this, so using the values in formula broke you could use =IF( LEN(E1)=5, E1, LEFT(E1,5) & "-" & MID(E1,6,4) ) "gh" wrote in message ... I have a column that has the zip codes in the cells. Some are 5 digit and some are 9 digit, without the - in them. They were a number stored as text and I changed them to a number. I formatted them using the format selection from the popup menu. I used special|zip code + 4. When I use a formula to put the address, city, state and zip together in another column the - is missing from the zip code (546789087). Also the 5 digit codes are not formatted correctly. Is there a formula that will check the code to be 5 or 9 and add the - to 9 digit code? What would cause the formatted code not to copy? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FormattingLost
I'm not sure why you want the zip codes to be pure numbers (instead of text)
as I doubt you will be doing any calculations with them; but, given that is your set up, if you want to mix 5-digit and 9-digit zip codes where your cell values are pure numbers, then use this Custom Format (instead of the Special Zip+4)... [99999]00000-0000;00000 and in your formulas, where you refer to a zip code cell (assumed to be A1 for this example), use this in place of that cell reference... TEXT(A1,"[99999]00000-0000;00000") -- Rick (MVP - Excel) "gh" wrote in message ... I have a column that has the zip codes in the cells. Some are 5 digit and some are 9 digit, without the - in them. They were a number stored as text and I changed them to a number. I formatted them using the format selection from the popup menu. I used special|zip code + 4. When I use a formula to put the address, city, state and zip together in another column the - is missing from the zip code (546789087). Also the 5 digit codes are not formatted correctly. Is there a formula that will check the code to be 5 or 9 and add the - to 9 digit code? What would cause the formatted code not to copy? TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|