extract text/words between string with wildcard and fix text string

[ploel]

hi
I would like to extract the following words from bank statements that are in a cell:

GIRO BANK RAIFFEISEN SCHWEIZ GENOSSENSCHAFT DORFSTRASSE 99 999999 ALIGDNSLIW CH9812349000003109099 Beatrice Tester Bernstrasse 99 1234 Bern SENDER REFERENCE

From here I would have to extract "Beatrice Tester" and "Bern" (underlined above)

GIRO BANK CH9909990456345323499 Tester Max Noah Zurich SENDER REFERENCE

From here I would have to extract "Tester Max" and "Zurich" (underlined above)

So I always have to extract the 2 words after the IBAN (right from CH*) and last word left from "SENDER". Count of words at beginning (up to IBAN) can differ as well as the IBAN itself differs and the count of words after sender reference as well differs.

Thanks for your help!

 

[DanteAmor]

Hi and welcome to MrExcel.

Try the following function:

Function ExtractWords(sText As String) As String
  Dim b As Variant, d As String, e As String
  Dim i As Long
 
  b = Split(sText, " ")
  For i = 0 To UBound(b)
    If UCase(Left(b(i), 2)) = "CH" And Len(b(i)) > 2 Then
      If IsNumeric(Mid(b(i), 3, 1)) Then
        d = b(i + 1) & " " & b(i + 2)
        Exit For
      End If
    End If
  Next
 
  b = Split(sText, " SENDER")
  e = Trim(Mid(b(0), InStrRev(b(0), " ")))
 
  ExtractWords = d & " " & e
End Function

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ExtractWords just like it was a built-in Excel function. For example:

Libro1
	A	B
1
2	GIRO BANK RAIFFEISEN SCHWEIZ GENOSSENSCHAFT DORFSTRASSE 99 999999 ALIGDNSLIW CH9812349000003109099 Beatrice Tester Bernstrasse 99 1234 Bern SENDER REFERENCE	Beatrice Tester Bern
3	GIRO BANK CH9909990456345323499 Tester Max Noah Zurich SENDER REFERENCE	Tester Max Zurich
Hoja1

Cell Formulas
Range	Formula
B2:B3	B2	=ExtractWords(A2)

 

[pgc01]

Hi

Another option

Function ExtractClient(s As String) As String

With CreateObject("VBScript.RegExp")
    .Pattern = ".*CH\d{19}\s(\w+ \w+).*?(\w+) SENDER.*"
    If .Test(s) Then ExtractClient = .Replace(s, "$1 $2")
End With
End Function

 

[DanteAmor]

Another option with excel formulas.

Libro1
	A	B	C
1
2	GIRO BANK RAIFFEISEN SCHWEIZ GENOSSENSCHAFT DORFSTRASSE 99 999999 ALIGDNSLIW CH9812349000003109099 Beatrice Tester Bernstrasse 99 1234 Bern SENDER REFERENCE	Beatrice Tester	Bern
3	GIRO BANK CH9909990456345323499 Tester Max Noah Zurich SENDER REFERENCE	Tester Max	Zurich
Hoja1

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH(" ",A2,SEARCH(" CH",A2)+1)+1,1000)," ",REPT(" ",100)),200))
C2:C3	C2	=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH("SENDER",A2)-2)," ",REPT(" ",100)),100))

 

[ploel]

great, thank you guys for your help!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[Rick Rothstein]

Here is another function that should also work...

Function ExtractWords(ByVal sText As String) As String
  Dim Parts() As String
  Parts = Split(Replace(Replace(sText, Chr(160), " "), "CH", "SENDER"), "SENDER")
  Parts = Split(Trim(Parts(UBound(Parts) - 1)))
  ExtractWords = Parts(1) & " " & Parts(2) & " " & Parts(UBound(Parts))
End Function