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

JJ Luke

New Member
Joined
Oct 5, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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

VBA Code:
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
 

Attachments

  • countries.jpg
    countries.jpg
    44.1 KB · Views: 20
Upvote 0
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
 
Upvote 0
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
AB
1AddressResult
230 Malta Road, London, United Kingdom, N16 7UGUnited Kingdom
316 Jersey Road, Leigham Manor Drive, Plymouth, United Kingdom, Ptt 2yaUnited Kingdom
425 France Hill, Kingston upon Thames, Australia, TE13 3TYAustralia
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",100)),200),100))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top