Excel (VBA?)- copy and paste 2 consecutive uppercase characters in string, if present

[machumpion]

Hi,

I have a range of full addresses, there are no common patterns separating city and state. The only pattern is that all the states are always two uppercase characters.

How can i search each cell in the range and paste the two letter state in the next column?

Thanks!

 

[stopher]

You could create a custom function like this:

Public Function getState(aString As String) As String
    Dim strLength As Integer
    Dim i As Integer
    
    strLength = Len(aString)
    getState = ""
    i = 1
    Do While i < strLength And getState = ""
        If Mid(aString, i, 2) = UCase(Mid(aString, i, 2)) Then
            getState = Mid(aString, i, 2)
        End If
        i = i + 1
    Loop
End Function

 

[machumpion]

hi stopher, thanks for your reply. your function seems to only be extracting the 2nd upper case latter in the target cell. There are other uppercase letters in the target cell as well, the only thing that distiguishes a state is that it consists of 2 consecutive upper case letters.

 

[stopher]

hi stopher, thanks for your reply. your function seems to only be extracting the 2nd upper case latter in the target cell. There are other uppercase letters in the target cell as well, the only thing that distiguishes a state is that it consists of 2 consecutive upper case letters.

Please show an example string and the result you got when you tried the function.

 

[machumpion]

i attached a screen capture using example data

 

[stopher]

Oops, though it was all alpha, my bad. Try:

Public Function getState(aString As String) As String
    Dim i As Integer
    
    getState = ""
    i = 1
    Do While i < Len(aString) And getState = ""
        If Asc(Mid(aString, i, 1)) >= 65 And Asc(Mid(aString, i, 1)) <= 90 Then
            If Asc(Mid(aString, i + 1, 1)) >= 65 And Asc(Mid(aString, i + 1, 1)) <= 90 Then
                getState = Mid(aString, i, 2)
            End If
        End If
        i = i + 1
    Loop
End Function

 

[machumpion]

thanks for quadrupling my productivity