Pulling text after finding its relative position

[hugomiguelnn]

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!

 

[Peter_SSs]

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]

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]

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)

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
	A	B
1	GUIDE, Paper congress 2564 US Zii	US
2	No code in this text
3
4	Geni Radio DOC RU, Team template	RU
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=CountryCode(A1)

 

[Rick Rothstein]

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

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]

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

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

 

[Rick Rothstein]

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

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. I think, though, that the code line needs to be this way to work correctly...

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

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

 

[hugomiguelnn]

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

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]

Did you try the post #4 function as well?