find 12 characters in a string

Function EXTRACT12(V) As String
    
    Dim i   As Long, Curr As String, x
    
    Curr = "US,GB,CH,HK"   '<< add more currency codes here
    
    If TypeOf V Is Range Then V = V.Value2
    x = Split(V)
    
    For i = 0 To UBound(x)
        If Len(x(i)) Then
            If InStr(1, Curr, Left(x(i), 2), 0) Then
                If Left(x(i), 3) Like "??#" Then
                    EXTRACT12 = x(i)
                    Exit Function
                End If
            End If
        End If
    Next
    
End Function

Select allOpen in new window

Option Explicit
Function GetCurrencyCode(testCell As Range)

    Dim myValue As String, curTxt As String
    GetCurrencyCode = "Not found"
    Dim xCell As Range, ix As Integer
    Dim vbSpace As String
    vbSpace = Chr$(32)
    myValue = testCell.Value
    
    'try to find a currency code in the text (preceded by space)
    For Each xCell In Range("CurrencyTable")
        curTxt = xCell.Value
        ix = InStr(1, myValue, vbSpace & curTxt)
        If ix > 0 Then
            'found the code at least
            
            'now check for exactly 10 characters before next space
            If InStr(ix + 2, myValue, vbSpace) = ix + 13 Then
                'extract the data
                GetCurrencyCode = Mid$(myValue, ix + 1, 12)
                Exit Function
            End If
        End If
        
    Next xCell
    
End Function

Select allOpen in new window