moving text from cell a to cell B and cell c

[2k05gt]

I have a Cell with some text the looks like this

A1 = Marshal of Omaha, NE
A2 = Baker of Lincolin, NE
A3 = Steil of Washington, DC
A4 = Favio of San Juan, PR

I want to split the cell data like this.

A1 = Marshal of B1 = Omaha C1 = NE
A2 = Baker of B2 = Lincolin C2 = NE
A3 = Steil of B3 = Washington C3 = DC
A4 = Favio of B4 = San Juan C4 = PR


How do I build this Maco? the word "of" and the comma "," are constents

I have tired the following

=IF(ISNUMBER(FIND("of",a1,1)),"of","")

=RIGHT(A1,LEN(A1)-FIND(", ",A1))

Also tried doing a VBA script
Sub Separate()
Dim City As String
Dim State As String
Dim Pos As Long

Pos = InStr(1, ActiveCell.Value, " ")
City = Mid(ActiveCell.Value, 1, Pos - 1)
State = Mid(ActiveCell.Value, Pos + 1)
ActiveCell.Value = City
ActiveCell.Offset(0, 1).Value = State

 

[VoG]

Maybe like this

Excel Workbook
	A	B	C	D
1	Marshal of Omaha, NE	Marshal of	Omaha	NE
2	Baker of Lincolin, NE	Baker of	Lincolin	NE
3	Steil of Washington, DC	Steil of	Washington	DC
Sheet2

 

[jmthompson]

or via VBA

Sub breakMe()
Dim c As Range
For Each c In Range("A1:A10")
If c <> "" Then
    With c
       .Offset(, 2) = Right(c, 2)
       .Offset(, 1) = Mid(c, WorksheetFunction.Find("of", c) + 3, (WorksheetFunction.Find(",", c) - (WorksheetFunction.Find("of", c) + 3)))
       .Offset(0, 0) = Left(c, WorksheetFunction.Find("of", c) + 1)
    End With
End If
Next c
End Sub

 

[2k05gt]

Thanks, Both worked Great..