Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Turning a 9 digit string into zip+4
Hi,
I have a column of data which contains 9 digit numbers. These are zip codes + 4. Problem is that we received the data as a continuous 9 digit number with no hyphen. For some other software we use, I really need the hyphen. I went to "Format Cell" and found zip+4 formatting and custom formating which shows "00000-0000." I tried to apply each and nothing happened. I even created another column, formatted with that cell format and then pasted the numbers in. Nothing. In other columns, I was able to reformat cell properties such as turning currency numbers into regular numbers. Little help? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Turning a 9 digit string into zip+4
Apparently , the 9 digits are pure "text" and not real numeric.
If you notice a small green rectangle at the corner of the cells - Select the range click on the small icon with the exclamation mark and choose "convert to number". Now, you will be able to format the cells as per zip codes. Micky "Attman68" wrote: Hi, I have a column of data which contains 9 digit numbers. These are zip codes + 4. Problem is that we received the data as a continuous 9 digit number with no hyphen. For some other software we use, I really need the hyphen. I went to "Format Cell" and found zip+4 formatting and custom formating which shows "00000-0000." I tried to apply each and nothing happened. I even created another column, formatted with that cell format and then pasted the numbers in. Nothing. In other columns, I was able to reformat cell properties such as turning currency numbers into regular numbers. Little help? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Turning a 9 digit string into zip+4
hi
if you received the data as 9 continuous digits, it may be formated as text which is why you cant assing a number format to them. but if you are using these zips with other software, formatting may not do you much good. formatting does not change data, it only changes the way the data looks on the sheet. underneith, it's still a continuous 9 digits. if you need to accually change the data, do this.... add a helper column next to your data and use this formula =LEFT(A2,5)&"-"&RIGHT(A2,4) adjust to suit. copy the formala down as far as you need. then copy the helper column and paste special values. delete the column with the 9 continuous digits. "Attman68" wrote: Hi, I have a column of data which contains 9 digit numbers. These are zip codes + 4. Problem is that we received the data as a continuous 9 digit number with no hyphen. For some other software we use, I really need the hyphen. I went to "Format Cell" and found zip+4 formatting and custom formating which shows "00000-0000." I tried to apply each and nothing happened. I even created another column, formatted with that cell format and then pasted the numbers in. Nothing. In other columns, I was able to reformat cell properties such as turning currency numbers into regular numbers. Little help? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Turning a 9 digit string into zip+4
Assuming FSt1 guessed what is happening with your raw data - try another
approach of changing your text: =REPLACE(A2,6,0,"-") Micky "Attman68" wrote: Hi, I have a column of data which contains 9 digit numbers. These are zip codes + 4. Problem is that we received the data as a continuous 9 digit number with no hyphen. For some other software we use, I really need the hyphen. I went to "Format Cell" and found zip+4 formatting and custom formating which shows "00000-0000." I tried to apply each and nothing happened. I even created another column, formatted with that cell format and then pasted the numbers in. Nothing. In other columns, I was able to reformat cell properties such as turning currency numbers into regular numbers. Little help? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the First Digit in a Text String | Charts and Charting in Excel | |||
How can I remove 5 digit from a string ? | Excel Discussion (Misc queries) | |||
Text Box - String to fill turning blank | Excel Discussion (Misc queries) | |||
turning a string of cells into a work shift (eg. 9am-7pm) | Excel Worksheet Functions | |||
How to find if a string starts with a digit | Excel Worksheet Functions |