Trying to extract two letters, the state to be more specific in cells that
have varying lenghts and number of spaces. Examples:
CEDAR RAPIDS IA 52404 USA
DANVILLE IL 61834 USA
FARGO ND 58102 USA
DES PLAINES IL 60016 USA
WOOD DALE IL 60191-1960 USA
So the only two letter combo between two spaces...
I need the states pulled out of these. Example
IA
IL
ND
IL
IL
Thanks
Assuming that your two letter state abbreviation will be the only two letter
combo that is followed by a <space> and then at least five digits; and also
that the two letters are capital letters, then this UDF is one way to do it:
<alt-F11> opens the VB Editor. Ensure your project is highlighted in the
Project Explorer window, then Insert/Module and paste the code below into the
window that opens.
To use this, enter the formula
=State(cell_reference)
into some cell. It should return the State abbreviation.
===============================================
Option Explicit
Function State(str As String) As String
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String = "\b[A-Z]{2}(?=\s+\d{5})"
Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern
If oRegex.test(str) = True Then
Set mcMatchCollection = oRegex.Execute(str)
State = mcMatchCollection(0)
End If
End Function
==============================================
If your format might be different then described above, the post back.
--ron
