Turning a 9 digit string into zip+4

[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

 

[FSt1]

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