separating zip codes

i'm trying to separate a column of 9 digit zip codes into 5 and 4. i've tried the data, text to columns option but the problem is zip codes that start with 0 - even when i format them, the text to columns application reads them as only 8 digits so they don't divide in the right place. suggestions?

thanks!

thanks, but there's still the problem of zip codes that start with 0. when i tried to do that, it treated 07940-2891 as 79402891, so that the first five were 79402.

You do have the cell format set to "text" correct?

Also, the zips are without dashes?


If so, then try this:

=IF(LEN(A1)=8,"0"&LEFT(A1,4),LEFT(A1,5))

=IF(LEN(A1)=8,MID(A1,5,4),MID(A1,6,4))

Try

=LEFT(TEXT(A1,"000000000"),5)

and

RIGHT(A1,4)