Remove Last 4 Characters in a Cell

[stanleytheyak]

I have a reporting tool that gives me zip codes in a strange format. It adds 4 zeros to the end of the zip code. So, for zip code 94517 it shows 945170000. Normally I would just do an =left(A1,5) and all would be right with the world. However, if the zip code starts with a 0, it drops the leading 0 and still adds the 4 zeros at the end. So for 02332 it shows 23320000.

Is there any way I can write a formula that just shows everything without the last 4 zeros? Bear in mind, that some of the zip codes have a 0 in them, so finding the first 0 isn't necessarily an option either...

Any help is greatly appreciated.

 

[Michael M]

Have you tried

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

 

[Rick Rothstein]

Another way...

=TEXT(A1/10000,"00000")

 

[stanleytheyak]

Another way...

=TEXT(A1/10000,"00000")

Rick, this worked perfectly! It even added the 0 in front of the 4 digit zip codes! THANK YOU!!!