You could also use a regex in a user defined function, which means you could execute in a loop over column A writing out to column B, or use in the sheet in column B. You can pass a list of the currencies of interest as the second argument to the function. If more than one currency, from your list, is present you get a comma separated list back.
Option Explicit
Public Sub Testing()
Dim tests(), i As Long, currencies()
currencies = Array("EUR", "GBP", "USD")
tests = Array("392898 818 AA 8812 1918281 000 EUR 100A", _
"31898 818 AA 88172 1281 000 GBP 1A", _
"12100098 618 AA 612 7281 000 USD 11100A")
For i = LBound(tests) To UBound(tests)
Debug.Print GetCurrency(tests(i), currencies)
Next
End Sub
Public Function GetCurrency(ByVal inputString As String, ByVal currencies As Variant) As String
Dim matches As Object, total As Long, arr(), i As Long
With CreateObject("VBScript.RegExp")
.Global = True
.MultiLine = True
.Pattern = "\b" & Join(currencies, Chr$(124)) & "\b"
If .TEST(inputString) Then
Set matches = .Execute(inputString): total = matches.Count - 1
ReDim arr(0 To total)
For i = 0 To total
arr(i) = matches(i)
Next
GetCurrency = Join(arr, Chr$(44))
Else
GetCurrency = vbNullString
End If
End With
End Function
Regex:
Try the regex here.
It basically uses the currencies in an OR fashion for matching. Match this currency or that currency etc. They are matched on being individual words and not part of a longer string.
/
\bEUR|GBP|USD\b
/
gm
1st Alternative \bEUR
\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)
EUR matches the characters EUR literally (case sensitive)
2nd Alternative GBP
GBP matches the characters GBP literally (case sensitive)
3rd Alternative USD\b
USD matches the characters USD literally (case sensitive)
\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)
In sheet:
You could store the array of currencies in a single cell and refer to that cell as the second function argument.
