Parsing (again)

[Jim Berglund]

AAINEY J 1193 STRATHCONA DR SW CALGARY, AB T3H4S1 (403) 293-2671
(403) 293-2671

I want to be able to extract the City. Province, and Postal Code from rows
like the above string. There are no fixed length fields.
I want to be able to use a variable in the final code to open a file, based
on the city name, so looking up CALGARY won't work. On the other hand, the
comma is common to all rows. so if I can search back from the comma to a
space, and forward 3 characters I'm OK. On the other hand, it won't handle
cities like LAS VEGAS, but I can ask users to name it LAS_VEGAS.

An option would be to get everything (CALGARY, AB T3H4S1) into one field. I
can then parse it out from there.

The code will look something like:
..Range("F1:F" & n).FormulaR1C1 = "=MID(R[0]C[-5],FIND("","",R[0]C[-5])+2,2)"
'I think this should get AB
..Range("G1:G" & n).FormulaR1C1 = "=MID(R[0]C[-6],FIND("","",R[0]C[-6])+5,6)"
' and this should extract T3H4S1


Thanks
Jim Berglund

 

[joel]

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