Can you give some example of the data  within the column.

You can find a separator with excel function find.special and use this
with vba  code and coninue searching from that point on to the next
separator until it run into the length of the string

The data  looks a little something like this

Firstname,Lastname- Chicago,IL- (5557774545)

I'm trying to get the names, city, state, and phone # separated. As
far as the find.special function, I do not see it anywhere. Any
clues?

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

I have had to separate  City and State before, and this is the code  that
I have used. Hopefully it will help  you get going:

Sub IdentifyState()
Dim strCell As String
Dim strState As String
Do While ActiveCell.Value <> ""
strCell = ActiveCell.Value
ActiveCell.Offset(0, 1).Range("A1").Select
strState = Right(strCell, 2)
ActiveCell.FormulaR1C1 = strState
ActiveCell.Offset(1, -1).Range("a1").Select
Loop

End Sub

In this example you used a fixed mechanisme to split the info. In this
case it's important to find where the separator is located. In a
previous answer i mentioned a function which can be used when you
perform this in a worksheet. In a macro this is converted to a search
function.
I think it should be something like

move value to string
loop
start looking for separator
start=1
found .. move value from start=1 to pos found to tempvalue
start looking for separator
start=posfound + 1
found .. move value from start to pos found to tempvalue-2
etc
until end of string
write out values to columns  and move on to the next row

If I have any time left I'll see if I can give provide a working sample

You may want to look at the Instr function

InStr([start, ]string1, string2[, compare])