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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
Deleted.........
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows
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

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

JJ Luke

New Member
Joined
Oct 5, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows
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))
 

JJ Luke

New Member
Joined
Oct 5, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

JJ Luke

New Member
Joined
Oct 5, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
You really have helped me enormously, so thanks one again.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,904
Messages
5,544,978
Members
410,647
Latest member
LegenDSlayeR
Top