Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
I need to write a code that extracts out of a string the city, state and zip.
An example of a string is: Las Vegas, Nv 89103 I need to an array variable to separate out the city, state and zip seperatly. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
ranswrt wrote:
I need to write a code that extracts out of a string the city, state and zip. An example of a string is: Las Vegas, Nv 89103 I need to an array variable to separate out the city, state and zip separatly. Thanks Try this ... sExample = " Las Vegas, Nv 89103 " aAddress = Split(Trim(sExample), ",") sTemp = aAddress(0) & "," _ & Replace(Replace(Trim(aAddress(1)), " ", " "), " ", ",") aAddress = Split(sTemp, ",") Tom Lavedas =========== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
I tried your code but I'm not familiar with how to use 'split'. What I am
trying to do is get the following array: address(1) = city address(2) = state address(3) = zip "tlavedas" wrote: ranswrt wrote: I need to write a code that extracts out of a string the city, state and zip. An example of a string is: Las Vegas, Nv 89103 I need to an array variable to separate out the city, state and zip separatly. Thanks Try this ... sExample = " Las Vegas, Nv 89103 " aAddress = Split(Trim(sExample), ",") sTemp = aAddress(0) & "," _ & Replace(Replace(Trim(aAddress(1)), " ", " "), " ", ",") aAddress = Split(sTemp, ",") Tom Lavedas =========== |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
I figured out how to seperate them out. Thanks
"tlavedas" wrote: ranswrt wrote: I need to write a code that extracts out of a string the city, state and zip. An example of a string is: Las Vegas, Nv 89103 I need to an array variable to separate out the city, state and zip separatly. Thanks Try this ... sExample = " Las Vegas, Nv 89103 " aAddress = Split(Trim(sExample), ",") sTemp = aAddress(0) & "," _ & Replace(Replace(Trim(aAddress(1)), " ", " "), " ", ",") aAddress = Split(sTemp, ",") Tom Lavedas =========== |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
On Thu, 31 Jul 2008 19:11:00 -0700, ranswrt
wrote: I tried your code but I'm not familiar with how to use 'split'. What I am trying to do is get the following array: address(1) = city address(2) = state address(3) = zip Here's one way that assumes your address strings are in the manner you presented it in your first post: <city<comma<space<state abbrev<space<zip code ================== Option Explicit Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr(1 To 3) As String Dim aTemp1, aTemp2 Dim i As Long aTemp1 = Split(Trim(sAdr), ",") aTemp2 = Split(Trim(aTemp1(1)), " ") aAdr(1) = aTemp1(0) aAdr(2) = aTemp2(0) aAdr(3) = aTemp2(1) For i = 1 To 3 Debug.Print aAdr(i) Next i End Sub =================== --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
Actually, he shows two blank spaces after the state abbreviation; that is, I
see this... <city<comma<space<state abbrev<space<space<zip code If that is correct, the code gets much easier. Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr() As String Dim i As Long aAdr = Split(Replace(sAdr, " ", ", "), ", ") For i = 0 To UBound(aAdr) Debug.Print aAdr(i) Next End Sub But even if you are right and there are not two blank spaces after the state abbreviation, we can still use the above structure with a slightly longer Split function call... Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr() As String Dim i As Long aAdr = Split(Replace(sAdr, Right(sAdr, 6), "," & Right(sAdr, 6)), ", ") For i = 0 To UBound(aAdr) Debug.Print aAdr(i) Next End Sub Rick "Ron Rosenfeld" wrote in message ... On Thu, 31 Jul 2008 19:11:00 -0700, ranswrt wrote: I tried your code but I'm not familiar with how to use 'split'. What I am trying to do is get the following array: address(1) = city address(2) = state address(3) = zip Here's one way that assumes your address strings are in the manner you presented it in your first post: <city<comma<space<state abbrev<space<zip code ================== Option Explicit Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr(1 To 3) As String Dim aTemp1, aTemp2 Dim i As Long aTemp1 = Split(Trim(sAdr), ",") aTemp2 = Split(Trim(aTemp1(1)), " ") aAdr(1) = aTemp1(0) aAdr(2) = aTemp2(0) aAdr(3) = aTemp2(1) For i = 1 To 3 Debug.Print aAdr(i) Next i End Sub =================== --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
On Fri, 1 Aug 2008 00:45:39 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: Actually, he shows two blank spaces after the state abbreviation; that is, I see this... <city<comma<space<state abbrev<space<space<zip code And you could also use regular expressions (of course). The code is longer, but it took me a fraction of the time to generate. It relies only on the <comma and the fact that there are three segments. Segment 1 is everything up to the <comma Segment 2 is the second string Segment 3 is the third string, which must be digits (The 2nd and 3rd strings could be separated by an optional <comma, for example). It would also be trivial to include error checking of various types, if needed. =============== Option Explicit Sub foo() Const sAdr As String = "Las Vegas, Nv , 89103" Dim aAdr(1 To 3) As String Dim re As Object, mc As Object, m As Object Dim i As Long Set re = CreateObject("vbscript.regexp") re.Pattern = "(^[^,]+)\W+(\w+)\W+(\d+)" If re.test(sAdr) Then Set mc = re.Execute(sAdr) For i = 0 To 2 aAdr(i + 1) = mc(0).submatches(i) Next i End If For i = 1 To 3 Debug.Print aAdr(i) Next i End Sub ==================== --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
On Jul 31, 10:11*pm, ranswrt
wrote: I tried your code but I'm not familiar with how to use 'split'. *What I am trying to do is get the following array: address(1) = city address(2) = state address(3) = zip That is exactly what Split does (see the VBA reference documents), except that the resulting array is zero based. That is the result is like this ... address(0) = city address(1) = state address(2) = zip All arrays in VBA are zero based, unless an Option Base statement changes it to 1. Tom Lavedas =========== |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
All arrays in VBA are zero based, unless an Option Base
statement changes it to 1. Except for the arrays generated by the Split function... the Split function **always** generates zero-based arrays no matter what the Option Base is set to. Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting Data out of a string
If the OP is not comfortable with using the Split function, then there is
always this VB code approach (there can be one or more blank spaces after the state abbreviation and it will still work)... Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr(1 To 3) As String Dim i As Long aAdr(1) = Left(sAdr, InStr(sAdr, ",") - 1) aAdr(2) = Trim(Mid(sAdr, InStr(sAdr, ",") + 1, 4)) aAdr(3) = Mid(sAdr, InStrRev(sAdr, " ") + 1) For i = 1 To 3 Debug.Print aAdr(i) Next End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Actually, he shows two blank spaces after the state abbreviation; that is, I see this... <city<comma<space<state abbrev<space<space<zip code If that is correct, the code gets much easier. Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr() As String Dim i As Long aAdr = Split(Replace(sAdr, " ", ", "), ", ") For i = 0 To UBound(aAdr) Debug.Print aAdr(i) Next End Sub But even if you are right and there are not two blank spaces after the state abbreviation, we can still use the above structure with a slightly longer Split function call... Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr() As String Dim i As Long aAdr = Split(Replace(sAdr, Right(sAdr, 6), "," & Right(sAdr, 6)), ", ") For i = 0 To UBound(aAdr) Debug.Print aAdr(i) Next End Sub Rick "Ron Rosenfeld" wrote in message ... On Thu, 31 Jul 2008 19:11:00 -0700, ranswrt wrote: I tried your code but I'm not familiar with how to use 'split'. What I am trying to do is get the following array: address(1) = city address(2) = state address(3) = zip Here's one way that assumes your address strings are in the manner you presented it in your first post: <city<comma<space<state abbrev<space<zip code ================== Option Explicit Sub foo() Const sAdr As String = "Las Vegas, NV 89103" Dim aAdr(1 To 3) As String Dim aTemp1, aTemp2 Dim i As Long aTemp1 = Split(Trim(sAdr), ",") aTemp2 = Split(Trim(aTemp1(1)), " ") aAdr(1) = aTemp1(0) aAdr(2) = aTemp2(0) aAdr(3) = aTemp2(1) For i = 1 To 3 Debug.Print aAdr(i) Next i End Sub =================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Data from a string of text in a cell | Excel Worksheet Functions | |||
extracting data from a string | Excel Worksheet Functions | |||
Formula for extracting data from a string | Excel Worksheet Functions | |||
Extracting Numeric Data from a Delimited Text String | Excel Worksheet Functions | |||
extracting data from a text string of varying length | Excel Discussion (Misc queries) |