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.
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.