Extract two letters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=MID(A1,FIND(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),A1)-3,2)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
Click to expand...

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
 
A rather naive approach:

Function myState(myCell)
n = Len(myCell)
For j = 1 To n
Debug.Print j
mytext = Mid(myCell, j, 1)
If IsNumeric(mytext) Then
k = j
Exit For
End If
Next j
If k > 1 Then
myState = Mid(myCell, k - 3, 2)
Else
myState = "N/A"
End If
End Function

best wishes
 
=MID(A1,FIND(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))),A1)-3,2)
Click to expand...

I thought of that approach, Bob, but around here we have towns with numbers in
the name, and that approach won't work.

One could do a formula approach picking the third word from the end, but it was
quicker to write a short UDF.


--ron
 
Ron Rosenfeld said:
I thought of that approach, Bob, but around here we have towns with
numbers in
the name, and that approach won't work.
Click to expand...

Really? How odd!
 
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
Click to expand...

If your state abbreviation is always the third "word" from the right, then this
UDF is even simpler:

==========================
Function State2(str As String) As String
Dim temp
temp = Split(Trim(str))
State2 = temp(UBound(temp) - 2)
End Function
=========================
--ron
 
If your state abbreviation is always the third "word" from the right, then this
UDF is even simpler:

==========================
Function State2(str As String) As String
Dim temp
temp = Split(Trim(str))
State2 = temp(UBound(temp) - 2)
End Function
=========================
--ron
Click to expand...

You might need to add some error checking, depending
--ron
 
Really? How odd!
Click to expand...

I just looked at a file of place names at the US Census web site (Had a crash
so I don't have the URL). In any event, there were over 230 names (out of
65-70,000) that included a number. Possibly only a few have zip codes.
--ron
 
It comes down to how many are there of those, as compared to how many
contain numbers as Ron mentioned.
 
Actually, the OP mentioned:
<<<"So the *only* two letter combo between two spaces...">>> (emphasis mine)
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Ragdyer said:
It comes down to how many are there of those, as compared to how many
contain numbers as Ron mentioned.
Click to expand...
 
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
Click to expand...

If the State code is always followed by a Zip Code and then a country
abbreviation, as in your examples, then the following formula might work:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1) - LEN(SUBSTITUTE(A1," ",""))-3))+1,2)


--ron
 
Interesting, that is what we call it in Sweden (translated of course), I
always thought you "Yanks" called it "Truth or Dare"

Maybe it was too hard to say? <vbg?



--
Regards,

Peo Sjoblom
 
Truth or Consequences = Truth or Dare in Swedish (Sanning eller Konsekvens)
Not just for teenage girls although scheming boys might have taken part for
other reasons than the girls. Combine it with a keg and it gets even more
interesting.

Somehow I cannot believe they are not related

Peo
 
How many around here are old enough to remember how it got that name ...
from a popular radio show in the early '50's.

I believe Ralph Edwards was the host and he got them to change it as a
publicity stunt.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Peo Sjoblom said:
Interesting, that is what we call it in Sweden (translated of course), I
always thought you "Yanks" called it "Truth or Dare"

Maybe it was too hard to say? <vbg?



--
Regards,

Peo Sjoblom
Click to expand...
 
Back
Top