Extract Postal code

[terrysoper1973]

Hi,

I have data separated by commas and want to extract a 7 character Postal code.

To the left of each record there are varing number of commas, but to the right there is only 1 comma. It's 7 characters containing the postal code from the left of that one single comma.

records look like this:

1245 Sherbrooke St 12th Fllor, Montreal H3G 1G3, QUEBEC
1245 Sherbrooke St 12th Fllor Montreal H3G 1G3, QUEBEC
1245, Sherbrooke St, 12th Fllor, Montreal H3G 1G3, QUEBEC

Where all I want is H3G 1G3

Can anyone help me out

Thanks
Terry

[daddylonglegs]

Hello Terry,

If your address is in A1 try this formula in B1 to extract the postal code

=MID(A1,LOOKUP(2^15,FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1)))))-7,7)

[MarvinP]

Hi,

Try this formula in B1 to see if it works for you.

Please Login or Register  to view this content.

[terrysoper1973]

Hi,

I have data separated by commas and want to extract a 7 character Postal code.

To the left of each record there are varing number of commas, but to the right there is only 1 comma. It's 7 characters containing the postal code from the left of that one single comma.

records look like this:

1245 Sherbrooke St 12th Fllor, Montreal H3G 1G3, QUEBEC
1245 Sherbrooke St 12th Fllor Montreal H3G 1G3, QUEBEC
1245, Sherbrooke St, 12th Fllor, Montreal H3G 1G3, QUEBEC

Where all I want is H3G 1G3

Can anyone help me out

Thanks
Terry

=MID(A1,LOOKUP(2^15,FIND(",",A1,ROW(INDIRECT("1:"&LEN(A1)))))-7,7)

This formula works great; can it be modified to extract the city that comes before the postal code? Can you recomend any books that would help me with functions and nesting them?

1245, Sherbrooke St, 12th Fllor, Montreal H3G 1G3, QUEBEC

[daddylonglegs]

Assuming you used the first formula I suggested in B1 then try this in C1 (note that it refers to the location of the first formula, change B1 if that's a different cell)

=TRIM(SUBSTITUTE(LEFT(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),199),99),B1,""))

[terrysoper1973]

Almost

record: 132 ALFRED ST SARNIA N7T 2G2, ONTARIO
Result: 132 ALFRED ST SARNIA
Where I'm looking to extract "SARNIA"

Gosh your good!

[MarvinP]

Hi terry (my assumption),

The problem is to find some rule that works for all your example data. You had two extra commas in your third example from the original post so that kept us from using them (the commas) to break up your string. The formula I gave was a hopeful hack. It looks like you used DLL's formula for the real work.

My formula started 10 characters from the end of the string, looking for a comma. I didn't know if it would work for every last word. It did work for "QUEBEC". After finding the last comma, the -7 jumped back to the front of the Postal Code and the +7 grabbed 7 letters, which should be the Postal Code.

My formula falls apart if the last word is St-Augustin-de-Desmaures as it is longer than 10 letters.
If the commas are like the most recent example, try this mamoth formula, If it works I can explain what it does.

Please Login or Register  to view this content.

[daddylonglegs]

My assumption was that the City would be preceded by a comma, as with the Montreal example, in the second example you gave that wasn't the case.

If there's no comma before the city then how do you determine where the city starts? This will work for all one word cities, just taking the word immediately before the postal code

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH(B1,A1)-2)," ",REPT(" ",99)),99))

[terrysoper1973]

Getting a #value! error

132 ALFRED ST SARNIA N7T 2G2, ONTARIO

I wasn't sure if your formula worked with ddl's or on the record above, so I tried it both ways.

Thanks

Dennis

[terrysoper1973]

Thanks Guys!

The data is very poorly formatted. But most of the cities are one word cities, so that cuts a lot of work out.

Thanks again

Dennis

[terrysoper1973]

I'm no expert at this by no means, but maybe this will be of use to someone else. To get 2 word city extractions, I used an exhausted city list and sorted it by number of characters. Then I used vlookup for the sorted list to extract the city name, using simular formula variations I got from moderators. There is probably a better way, but my knowledge is very narrow.


=LOOKUP(2^15,SEARCH(cities!$A$1:$A$8252,MID(E2,LOOKUP(2^15,FIND(",",E2,ROW(INDIRECT("1:"&LEN(E2)))))-20,20)),cities!$A$1:$A$8252)

Thanks Moderators!

Terry