I am attempting to manipulate data that is imported in an Excel spreadsheet.
Some fields contain a zip code, some contain zip code + 4 and some don't
contain any zip code. I have to export the data to a system that requires a
standard 5 digit zip code only (for all data). I have used the following
formula to correct data rows that have no zip code, but I'm not sure how to
get rid of the +4 if they are present. I tried using trim and mid without
much luck. Likely I'm just not getting the formula correct.

Here's the current formula:
=IF(Millenia!U2="",99999,IF(Millenia!U210000,Mill enia!U2,CONCATENATE(Millenia!U2,1)))

This fixes the issue of no zip code or a zip code with one missing number
(not best fix but it works to get data uploaded). The question is, how can I
modify this formula to trip the +4 when and if present?

Thanks in advance for your help!

You can try and use the Left function

=IF(Millenia!U2="",99999,IF(Millenia!U210000,Mill enia!U2,LEFT(Millenia!U2,5))

"Pat" wrote:

I am attempting to manipulate data that is imported in an Excel spreadsheet.
Some fields contain a zip code, some contain zip code + 4 and some don't
contain any zip code. I have to export the data to a system that requires a
standard 5 digit zip code only (for all data). I have used the following
formula to correct data rows that have no zip code, but I'm not sure how to
get rid of the +4 if they are present. I tried using trim and mid without
much luck. Likely I'm just not getting the formula correct.

Here's the current formula:
=IF(Millenia!U2="",99999,IF(Millenia!U210000,Mill enia!U2,CONCATENATE(Millenia!U2,1)))

This fixes the issue of no zip code or a zip code with one missing number
(not best fix but it works to get data uploaded). The question is, how can I
modify this formula to trip the +4 when and if present?

Thanks in advance for your help!

Hi,

Unless I misinterpret your question, you will find this solution works for you

=IF(A1="","99999",RIGHT("0000"&LEFT(A1,5),5))

Replace the references to A1 with your Millenia!U2 ones. Also, depending on
details you may be able to remove the quotes around 99999.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Pat" wrote:

I am attempting to manipulate data that is imported in an Excel spreadsheet.
Some fields contain a zip code, some contain zip code + 4 and some don't
contain any zip code. I have to export the data to a system that requires a
standard 5 digit zip code only (for all data). I have used the following
formula to correct data rows that have no zip code, but I'm not sure how to
get rid of the +4 if they are present. I tried using trim and mid without
much luck. Likely I'm just not getting the formula correct.

Here's the current formula:
=IF(Millenia!U2="",99999,IF(Millenia!U210000,Mill enia!U2,CONCATENATE(Millenia!U2,1)))

This fixes the issue of no zip code or a zip code with one missing number
(not best fix but it works to get data uploaded). The question is, how can I
modify this formula to trip the +4 when and if present?

Thanks in advance for your help!