Extracting Data out of a string

[ranswrt]

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

 

[tlavedas]

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

 

[ranswrt]

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

 

[ranswrt]

I figured out how to seperate them out. 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
===========

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[T Lavedas]

I tried your code but I'm not familiar with how to use 'split'.  What Iam
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
===========

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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