Searching for a country in address data, but right to left

[JJ Luke]

Hi,

I'm using a macro helpully provided by hiker95 on a previous thread some years ago, but could do with a bit of help please:

It looks through col A which contains raw e-com generated address data held in one string.

It uses a list of countries in col C to find matches and adds the country found in Col B

My problem is that some addresses contain a country name in road or region name such as:

20 Malta lane, London, United Kingdom

As it stands the Macro finds the first match and puts that in Col B - in the above example: 'Malta', when it should be @united Kingdom'

Is there anyway to make this macro search right to left so that the first match will always be the country?

MACRO:

Sub ExtractCountry()
' hiker95, 12/15/2013
' Searching for a country in a cell's text string and having the result returned
Dim a As Variant, c As Variant
Dim i As Long, ii As Long
a = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
c = Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
For i = 1 To UBound(c, 1)
For ii = 1 To UBound(a, 1)
If InStr(a(ii, 1), Trim(c(i, 1))) Then a(ii, 2) = c(i, 1)
Next ii
Next i
Range("A2").Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

Many thanks for reading.

 

[mohadin]

Deleted.........

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you give us some sample dummy data and the expected results with XL2BB?
That way we would have a better idea of your data layout and requirement and also have some easily copied data to test with.

 

[JJ Luke]

Hi Peter_SSs, thanks for the welcome and help.

Here's a screenshot (not sure how to paste cols, although have tried below) of a slim-downed list of countries and after the macro has run.

in row 1, for some reason it works and gives 'United Kingdom' as the country in Col B
in row 2 it fails and gives 'Jersey' as the Country in Col B, when it should be 'United Kingdom'
in row 3, it fails and gives 'France' as the country in Col B, when it should be 'Australia'

Many thanks for looking at this.

Luke


Here's a slimmed down version of the data

Address    Result    Countries list
30 Malta Road, London, United Kingdom, N16 7UG    United Kingdom    Australia
16 Jersey Road, Leigham Manor Drive, Plymouth, United Kingdom, Ptt 2ya    Jersey    France
25 France Hill, Kingston upon Thames, Australia, TE13 3TY    France    Jersey
        United Kingdom

 

[JJ Luke]

Just to add, if its not possible to search right to left, could the macro output all matches instead of just one? Then I could have an easy way of manually checking for errors...

Thanks

 

[Peter_SSs]

Here's a screenshot (not sure how to paste cols,

Follow the hyperlink in my previous post.
Also asked for the expected results, not the incorrect ones.

Do you really need the country list? Isn't the required answer what comes between the second last comma and the last comma?
If that is so could you use this formula instead of a macro?

JJ Luke.xlsm
	A	B
1	Address	Result
2	30 Malta Road, London, United Kingdom, N16 7UG	United Kingdom
3	16 Jersey Road, Leigham Manor Drive, Plymouth, United Kingdom, Ptt 2ya	United Kingdom
4	25 France Hill, Kingston upon Thames, Australia, TE13 3TY	Australia
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",100)),200),100))

 

[JJ Luke]

Sorry Peter - I should have read that link. I'm off to try the formula you kindly provided and check the raw data to see if there's always a postcode after the last comma - will be back in some hours as childcare intervenes.

Much appreciated.

Luke

 

[JJ Luke]

I found an unexpected gap to try the formula and check the data. The formula works like a dream and the raw data always has a postcode - thanks so much!!

Can I 'buy you a beer' Peter?

Thanks

Luke

 

[Peter_SSs]

The formula works like a dream and the raw data always has a postcode - thanks so much!!

You're welcome. Thanks for the follow-up.

Can I 'buy you a beer' Peter?

Perhaps when covid-19 allows us to travel again? ?

 

[JJ Luke]

You really have helped me enormously, so thanks one again.