Eliminate trailing zeros in zip codes

  • Thread starter Thread starter Karl H
  • Start date Start date
K

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
 
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?
 
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 said:
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
Click to expand...
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"
 
Thanks, Peo...that should do it.
Karl

Peo Sjoblom said:
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
Click to expand...
 
I like it--easier than code-thank you!
Karl

*alan* said:
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"
Click to expand...
 
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
Click to expand...

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
 
Back
Top