Find the first two Uppercase letters in a string and return that value

Enter formula in B1 and copy down

Formula:

=IFERROR(MID(A1,SEARCH(" ?? ",A1&" ")+1,2),"")

v	A	B
1	Kansas City KS	KS
2	Denver CO company name	CO
3	Salt Lake City UT companyname	UT
4	Southern Cal
5	Fairfield OH RET/SUPP CHN	OH
6	St. Louis MO	MO

Originally Posted by CrazyPerson

It would be useful to figure out to find the two uppercase letters in the string

with AlKey formula:

Formula:

=IF(EXACT(IFERROR(MID(A1,SEARCH(" ?? ",A1&" ")+1,2),""),UPPER(IFERROR(MID(A1,SEARCH(" ?? ",A1&" ")+1,2),""))),IFERROR(MID(A1,SEARCH(" ?? ",A1&" ")+1,2),""),"")

but it's not solution for : Kansas City ab KS

==
With UDF VBA regex (for now and future):

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5" (IMPORTANT!)

• Select "Developer" tab
• Select "Visual Basic" icon from 'Code' ribbon section
• In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
• Select "References"
• Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
• Click "OK"

Step 2: Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below.
Step 3: In the big text window in the middle insert the following:

Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
    Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
    Dim replaceNumber As Integer

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With
    With outputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "\$(\d+)"
    End With
    With outReplaceRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With

    Set inputMatches = inputRegexObj.Execute(strInput)
    If inputMatches.Count = 0 Then
        regex = "" 'False
    Else
        Set replaceMatches = outputRegexObj.Execute(outputPattern)
        For Each replaceMatch In replaceMatches
            replaceNumber = replaceMatch.SubMatches(0)
            outReplaceRegexObj.Pattern = "\$" & replaceNumber

            If replaceNumber = 0 Then
                outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).value)
            Else
                If replaceNumber > inputMatches(0).SubMatches.Count Then
                    regex = CVErr(xlErrValue)
                    Exit Function
                Else
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                End If
            End If
        Next
        regex = outputPattern
    End If
End Function

Step 4: Save and close the Microsoft Visual Basic for Applications Editor window.
usage: =regex(A1,"your pattern")

in this case:

Formula:

=TRIM(regex(A1,"([A-Z][^a-z]{2})|([A-Z]{2})$"))

with this code you can define much more patterns not only as simply as ([A-Z][^a-z]{2})|([A-Z]{2})$")