Pulling text after finding its relative position

hugomiguelnn

New Member
Joined
Mar 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Dear all,

I am stuck trying to automate a report which contains long strings of data with about 60 different country codes variants.

I have a column text based which is inconsistent, for example:
1) GUIDE, Paper congress 2564 US Zii
2) Geni Radio DOC RU, Team template

What I need to do, is to automate the task of pulling out the country code to a single cell. For example:
Cell 1) I would want to pull out US, and in
Cell 2) I would like to pull out RU.


The positioning of the countries is never consistent, and sometimes it is a "," next to it, or sometimes it ends the sentence, or is in the middle, etc. It is however always in capital letters.

I have been trying to nesting Vlookup function, index function, Match function, Find function, however I do not seem to be getting closer to figure this out. I can find the positioning of the word, however I am not able to pull the text out.

I find the word positioning the following way:
=OR(ISNUMBER(FIND(" US ";" "&A2&" "));ISNUMBER(FIND(" BR ";" "&A2&" "))

I just cant figure out how to pull out the value in a text base form. I am hoping someone here has similar experience and can guide me through it :)

Thanks!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
It is however always in capital letters.
But "GUIDE" and "DOC" are also in capital letters in your examples. What is the logic of determining what is a country code and what is not?
Or do you have a list of country codes somewhere in your workbook?
 

hugomiguelnn

New Member
Joined
Mar 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
But "GUIDE" and "DOC" are also in capital letters in your examples. What is the logic of determining what is a country code and what is not?
Or do you have a list of country codes somewhere in your workbook?
The logic is all countries are written in its abbreviation, such as RU for Russia or US for USA or ES for Spain.

Some files have capital letters therefore the text in the cell is completely inconsistent. I do have a list of the countries, however I have not found it useful for this purpose.. not yet at least. I thought a Vlookup could help in the beginning, but I do not manage to make it work. With my formula I can find all country codes location as value in all cells, it does tell me every column in which a country is. However I do not manage to pull out this information as text which is my end goal :(
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function CountryCode(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "\b[A-Z]{2}\b"
    If .Test(s) Then CountryCode = .Execute(s)(0)
  End With
End Function

hugomiguelnn.xlsm
AB
1GUIDE, Paper congress 2564 US ZiiUS
2No code in this text 
3 
4Geni Radio DOC RU, Team templateRU
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=CountryCode(A1)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

If I had to guess, I'd say Peter's function is probably faster then mine, but you most likely would not notice the difference. Anyway, in case you wanted to see a solution that did not use Regular Expressions, here is one...
VBA Code:
Function CountryCode(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, 4) Like "[!A-Z][A-Z][A-Z][!A-z]" Then
      CountryCode = Mid(S, X, 2)
      Exit For
    End If
  Next
End Function
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
@Rick Rothstein
Hi Rick. Might be safer with this in case text like "abCD ef GH" was possible?

Rich (BB code):
If Mid(" " & S & " ", X, 4) Like "[!A-z][A-Z][A-Z][!A-z]" Then
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,969
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Rick Rothstein
Hi Rick. Might be safer with this in case text like "abCD ef GH" was possible?

Rich (BB code):
If Mid(" " & S & " ", X, 4) Like "[!A-z][A-Z][A-Z][!A-z]" Then
Where is that "Bang Head" smilie when you need it. :biggrin: I think, though, that the code line needs to be this way to work correctly...
Excel Formula:
If Mid(" " & S & " ", X, 4) Like "[!A-Za-z0-9][A-Z][A-Z][!A-Za-z0-9]" Then
So the OP doesn't have to piece it together (assuming he wants to use my code), here is the corrected function in full...
VBA Code:
Function CountryCode(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, 4) Like "[!A-Za-z0-9][A-Z][A-Z][!A-Za-z0-9]" Then
      CountryCode = Mid(S, X, 2)
      Exit For
    End If
  Next
End Function
 
Solution

hugomiguelnn

New Member
Joined
Mar 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
If I had to guess, I'd say Peter's function is probably faster then mine, but you most likely would not notice the difference. Anyway, in case you wanted to see a solution that did not use Regular Expressions, here is one...
VBA Code:
Function CountryCode(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, 4) Like "[!A-Z][A-Z][A-Z][!A-z]" Then
      CountryCode = Mid(S, X, 2)
      Exit For
    End If
  Next
End Function
Oh wauw this is good! After this had to start playing a little with VBA, it is definitely the way to go! Thanks a lot!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,471
Office Version
  1. 365
Platform
  1. Windows
Did you try the post #4 function as well?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,352
Messages
5,635,785
Members
416,882
Latest member
ericvrealty

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
Top