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