try this

Sub test()

Addr = "AAINEY J 1193 STRATHCONA DR SW CALGARY, AB T3H4S1 (403)
293-2671 (403) 293-2671"
CommaPosition = InStr(Addr, ",")
'remove everything left of the comma
City = Trim(Left(Addr, CommaPosition - 1))
LastSpace = InStrRev(City, " ")
City = Mid(City, LastSpace + 1)

Providence = Trim(Mid(Addr, CommaPosition + 1))
Firstspace = InStr(Providence, " ")
Providence = Left(Providence, Firstspace - 1)


End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=206222

http://www.thecodecage.com/forumz