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

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
===========

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
===========

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
===========

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

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

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

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
===========

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

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