I am trying to separate city, state, zip from 1 cell to 3 by using the IF
function. But I cannot get the IF function to recognize text values, only
numeric. I have already changed the cells format to text, but to no avail.
Any suggestions? Can it be done in excel?

Hi
see your other post

--
Regards
Frank Kabel
Frankfurt, Germany

BarnyardJoe wrote:
I am trying to separate city, state, zip from 1 cell to 3 by using
the IF function. But I cannot get the IF function to recognize text
values, only numeric. I have already changed the cells format to
text, but to no avail. Any suggestions? Can it be done in excel?

if you are using xl2000 or later

Sub Addr()
Dim sStr As String, v As Variant
Dim lCity As Long, zip As String
Dim city As String, state As String
sStr = "Los Angeles, CA 90210"
Range("A1").Value = sStr
v = Split(Range("A1").Value, " ")

lCity = UBound(v) - 2
zip = Trim(v(UBound(v)))
state = Trim(v(UBound(v) - 1))
city = ""
For i = 0 To lCity
city = city & Trim(v(i)) & " "
Next
city = Trim(Replace(city, ",", ""))
Debug.Print city, state, zip

End Sub

for string like

Los Angeles, CA 90210


--
Regards,
Tom Ogilvy


"BarnyardJoe" wrote in message
...
I am trying to separate city, state, zip from 1 cell to 3 by using the IF
function. But I cannot get the IF function to recognize text values, only
numeric. I have already changed the cells format to text, but to no
avail.
Any suggestions? Can it be done in excel?