try this Statement bellow,
put original data  in column  A, basic form : Firstname,Lastname- Chicago,IL-
(5557774545)

the result :
Column C = Firstname,Lastname, B = Chicago, Column D = IL, column E =
5557774545

It's easy !


Sub Separate()

Dim var1, var2, Var3, var4, Var5, Var6



var2 = 1



Do

var1 = Range("A" & var2)

If var1 = "" Then Exit Do

Var3 = InStr(var1, "-")

Range("B" & var2) = Left(var1, Var3 - 1)

var4 = InStrRev(var1, ",")

Range("C" & var2) = Trim(Mid(var1, Var3 + 1, var4 - Var3 - 1))

Var5 = InStrRev(var1, "-")

Range("D" & var2) = Mid(var1, var4 + 1, Var5 - var4 - 1)

Var6 = InStrRev(var1, "(")

Range("E" & var2) = Mid(var1, Var6 + 1, Len(var1) - Var6 - 1)

var2 = var2 + 1

Loop Until var1 = ""



ActiveSheet.Columns(5).NumberFormat = "General"



End Sub