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