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