Eliminate trailing zeros in zip codes

[Karl H]

If a zip code ends with 4 zeros, I'd like to eliminate them and have the zip
code just be 5 numbers (or text). I'm not sure how to do this If/then
statement. Here's what I have (which doesn't work).

=IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))

Thank you in advance for any help.
Karl

 

[Peo Sjoblom]

Maybe

=IF(AND(LEN(J2)=10,RIGHT(J2,4)="0000"),LEFT(J2,5),J2)



--


Regards,


Peo Sjoblom

 

[Karl H]

Thank you, but it didn't work. That turned "32401" to 00003-2401 and
324010000 to 32401-0000.
I wonder if the "0000" is just being interpreted as any 4 numeric characters?

 

[Peo Sjoblom]

No it does not turn 32401 to 00003-2401, That is just the display not the
real values. You didn't say these were numerical values, all you have to do
if they are numerical values is to change the format to 00000




--


Regards,


Peo Sjoblom

 

[*alan*]

If a zip code ends with 4 zeros, I'd like to eliminate them and have the
zip
code just be 5 numbers (or text). I'm not sure how to do this If/then
statement. Here's what I have (which doesn't work).

=IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))

Thank you in advance for any help.
Karl

How about just using the Find and Replace function (CTRL+F) ??

Assuming your zipcodes are expressed normally (i.e. 12345-1234 and
12345-0000):

Find what: enter -0000
Replace with: leave blank
Click "Replace All"

 

[Karl H]

Thanks, Peo...that should do it.
Karl

No it does not turn 32401 to 00003-2401, That is just the display not the
real values. You didn't say these were numerical values, all you have to do
if they are numerical values is to change the format to 00000




--


Regards,


Peo Sjoblom

 

[Karl H]

I like it--easier than code-thank you!
Karl

How about just using the Find and Replace function (CTRL+F) ??

Assuming your zipcodes are expressed normally (i.e. 12345-1234 and
12345-0000):

Find what: enter -0000
Replace with: leave blank
Click "Replace All"

 

[Ron Rosenfeld]

If a zip code ends with 4 zeros, I'd like to eliminate them and have the zip
code just be 5 numbers (or text). I'm not sure how to do this If/then
statement. Here's what I have (which doesn't work).

=IF(J2 Right(="0000"), Text("00000"), Text("00000/-####))

Thank you in advance for any help.
Karl

If your ZIP codes are entered as numbers, then you can either use the custom
format:

Format/Cells/Number/Custom Type: [<100000]00000;00000-0000

OR the formula:

=TEXT(A1,"[<100000]00000;00000-0000")


--ron