Extracting Data out of a string

  • Thread starter Thread starter ranswrt
  • Start date Start date
R

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
 
ranswrt said:
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
Click to expand...

Try this ...

sExample = " Las Vegas, Nv 89103 "
aAddress = Split(Trim(sExample), ",")
sTemp = aAddress(0) & "," _
& Replace(Replace(Trim(aAddress(1)), " ", " "), " ", ",")
aAddress = Split(sTemp, ",")

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

tlavedas said:
Try this ...

sExample = " Las Vegas, Nv 89103 "
aAddress = Split(Trim(sExample), ",")
sTemp = aAddress(0) & "," _
& Replace(Replace(Trim(aAddress(1)), " ", " "), " ", ",")
aAddress = Split(sTemp, ",")

Tom Lavedas
===========
Click to expand...
 
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
Click to expand...

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
 
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
 
Actually, he shows two blank spaces after the state abbreviation; that is, I
see this...

<city><comma><space><state abbrev><space><space><zip code>
Click to expand...

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
 
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
Click to expand...

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
===========
 
All arrays in VBA are zero based, unless an Option Base
statement changes it to 1.
Click to expand...

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
 
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
 
Back
Top