| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract two letters
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
=MID(A1,FIND(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),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) "bm" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
On Thu, 9 Aug 2007 14:11:57 +0100, "Bob Phillips"
wrote: =MID(A1,FIND(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))),A1)-3,2) 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
"Ron Rosenfeld" wrote in message ... On Thu, 9 Aug 2007 14:11:57 +0100, "Bob Phillips" wrote: =MID(A1,FIND(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))),A1)-3,2) I thought of that approach, Bob, but around here we have towns with numbers in the name, and that approach won't work. Really? How odd! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
On Thu, 9 Aug 2007 16:35:40 +0100, "Bob Phillips"
wrote: "Ron Rosenfeld" wrote in message .. . On Thu, 9 Aug 2007 14:11:57 +0100, "Bob Phillips" wrote: =MID(A1,FIND(LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))),A1)-3,2) I thought of that approach, Bob, but around here we have towns with numbers in the name, and that approach won't work. Really? How odd! 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
On Thu, 9 Aug 2007 05:56:01 -0700, bm wrote:
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "bm" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
On Thu, 9 Aug 2007 05:56:01 -0700, bm wrote:
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 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
On Thu, 09 Aug 2007 11:38:07 -0400, Ron Rosenfeld
wrote: On Thu, 9 Aug 2007 05:56:01 -0700, bm wrote: 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 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 You might need to add some error checking, depending --ron |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
Does this work for you:
=MID(A1,SEARCH(" ?? ",A1)+1,2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bm" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
Until you get to King of Prussia, Pa.
Ragdyer wrote: Does this work for you: =MID(A1,SEARCH(" ?? ",A1)+1,2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bm" wrote in message ... 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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
It comes down to how many are there of those, as compared to how many
contain numbers as Ron mentioned. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Until you get to King of Prussia, Pa. Ragdyer wrote: Does this work for you: =MID(A1,SEARCH(" ?? ",A1)+1,2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bm" wrote in message ... 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 -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
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" wrote in message ... It comes down to how many are there of those, as compared to how many contain numbers as Ron mentioned. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Until you get to King of Prussia, Pa. Ragdyer wrote: Does this work for you: =MID(A1,SEARCH(" ?? ",A1)+1,2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bm" wrote in message ... 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 -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
http://www.floydpinkerton.net/fun/citynames.html
nothing really to do with this thread except that US city names can obviously be called anything, I like Intercourse, PA another one I remember with regards to this thread Avon By The Sea Peo "Ragdyer" wrote in message ... It comes down to how many are there of those, as compared to how many contain numbers as Ron mentioned. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Until you get to King of Prussia, Pa. Ragdyer wrote: Does this work for you: =MID(A1,SEARCH(" ?? ",A1)+1,2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bm" wrote in message ... 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 -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
I liked this one:
http://maps.google.com/maps?q=Truth+...map& ct=title Peo Sjoblom wrote: http://www.floydpinkerton.net/fun/citynames.html nothing really to do with this thread except that US city names can obviously be called anything, I like Intercourse, PA another one I remember with regards to this thread Avon By The Sea Peo "Ragdyer" wrote in message ... It comes down to how many are there of those, as compared to how many contain numbers as Ron mentioned. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Until you get to King of Prussia, Pa. Ragdyer wrote: Does this work for you: =MID(A1,SEARCH(" ?? ",A1)+1,2) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bm" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract two letters
On Thu, 9 Aug 2007 05:56:01 -0700, bm wrote:
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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract letters from a cell | Excel Worksheet Functions | |||
| How do change a column of data in capitol letters to small letters | Excel Discussion (Misc queries) | |||
| Extract bold letters in a cell | Excel Worksheet Functions | |||
| Extract bold letters in a cell | Excel Worksheet Functions | |||
| Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
Hybrid Mode
