Find Replace/Join based on Min and Max Zip code

[Lucasvital]

Hi,

I have the zip code field in my excel file A that I want match (either find and replace, or join) to a specific field in excel file B based on zip code; however, zip codes in file B are based on a range of Min and Max (split in two columns). Any tips on easiest way to solve? I've been trying to create some if formulas based on zip code values but might exist an easier solution than that.

Thanks

[MichalM]

@Lucasvital 

Could you provide an example? A sample input and desired output?

[Lucasvital]

Sure...

1- Example:

Sample Input A: 

Field Id - 123 

Country - United States

State - Florida

Field Zip code - 00080

Sample Input B

Field Name - abcabc

Country - United States

State - Florida

Field zip Code Max - 00100

Field Zip code Min - 00000

Output:

Field Id 123

Field Name - abcabc

Field Zip code - 00080

Country - United States

State - Florida

2- In case it's possible to go to next level of bringing the city name based on zip code:

Output:

Field Id 123

Field Name - abcabc

Field Zip code - 00080

Country - United States

State - Florida

City - zzzzz

[MichalM · Accepted solution]

@Lucasvital 

I'm assuming here so please correct me if I'm wrong - if we increment by 1 from Min (0) to Max (100) - we'll get all the Florida Zip codes, correct? If that's the case, we can use the Generate Rows tool to achieve this.

The result will be a set of numbers from 0 to 100 which then need to be padded on left with 0 to complete the full Zip - we achieve that using the formula tool.

PadLeft(ToString([Zip]), 5, "0")

Once ready, we can use the Join tool to append the Field Name based on a matching Zip.

Example attached.

[Lucasvital]

Hi Michal

Thank you very much - I have neither used "Generate Rows" function nor "PadLeft" as formula before - this definitely solves for US, Germany and other countries from which the ZIp Codes are only numerical (most of my database), but not for UK and Canada as the formats for zip codes are the following:

Non-Matched zip codes:

UK/London 6 or more digits (Mix of Integers and strings)
e.g. Low: BR0 0AA vs High: BR9 9ZZ

Canada - 7 digits (Mix of 3 Integers, 3 strings and one blank)

e.g. Low: R0A 0A0 vs High: R9Z 9Z9

Any thoughts?

Thanks again!

[MichalM]

@Lucasvital 

You could potentially invent some logic to increment these but I think it would make more sense to introduce a lookup table with a postcode - state / postcode - city mapping and use that instead.