Turning a 9 digit string into zip+4

  • Thread starter Thread starter Attman68
  • Start date Start date
A

Attman68

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!
 
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
 
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.
 
Assuming FSt1 guessed what is happening with your raw data - try another
approach of changing your text:
=REPLACE(A2,6,0,"-")
Micky
 
Back
Top