Hi Pat, sorry about the delay in this response but I signed off last night
before you sent it. The compile problem you're encountering is being caused
by the automatic wrapping of the code by this response window. When you copy
and paste that wrapped code into your Excel VBA editor, it causes a compile
error because you must explicitly identify wrapped code (in the editor) by
entering "_ " at the end of each wrapped code line. So, the code should look
like what you see below (you can copy and paste the code below straight into
the VBA editor). Incidentally, Ron's code is far more elegant and
professional so if you're looking to learn VBA, be sure to take a close look
at what he wrote. Mine is just quick and dirty but will get you what you
need.

Sub ParseAddress()

'Parse comma-delimited city,state,zip data
Do While ActiveCell.Value < ""
'City
ActiveCell.Offset(0, -2).Value = VBA.Left(ActiveCell.Value, _
Application.WorksheetFunction.Find(",", ActiveCell.Value) - 1)
'State
ActiveCell.Offset(0, -1).Value = VBA.Mid(ActiveCell.Value, _
Application.WorksheetFunction.Find(",", ActiveCell.Value) + 1, 2)
'Zip Code
ActiveCell.Value = "'" & VBA.Right(ActiveCell.Value, 5)
'Select next data record
ActiveCell.Offset(1, 0).Select
Loop


End Sub

"pattlee" wrote:

Hi Paul, Ran this code and during Debug got error message at line
ActiveCell.Offset(0, -2).Value = VBA.Left(ActiveCell.Value,
Please advise... Patt Am including a few lines of actual Data .
E F G
City STATE ZIP
JACKSONVILLE, FL 32224
CHARLESTON, SC 29405
HIALEAH, FL 33016
OCALA, FL 34478-3200
RICHLAND, WA 99352
MIAMI, FL 33138


"Paul Mathews" wrote:

Hi Pat,

The code below while not the most elegant, should get you what you need (be
sure to make a copy of your original data since the parsed zip code data will
overwrite the original, unparsed data in column G). Note that, before you
run the macro, you need to set focus on the very first data record that you
want to parse in column G. The macro will iteratively run down the data list
and generate the parsed data in columns E, F and G as you need. Column G
should not contain embedded blank data items (if it does, the macro will stop
when it hits the blank record and you'll need to set focus on the next
non-blank data item and run the macro from that point forward; if you have no
embedded blank data records, the macro will stop after the last data record).

Paul

Sub ParseAddress()

'Parse comma-delimited city,state,zip data
Do While ActiveCell.Value < ""
ActiveCell.Offset(0, -2).Value = VBA.Left(ActiveCell.Value,
Application.WorksheetFunction.Find(",", ActiveCell.Value) - 1)
ActiveCell.Offset(0, -1).Value = VBA.Mid(ActiveCell.Value,
Application.WorksheetFunction.Find(",", ActiveCell.Value) + 1, 2)
ActiveCell.Value = "'" & VBA.Right(ActiveCell.Value, 5)
ActiveCell.Offset(1, 0).Select
Loop


End Sub

"pattlee" wrote:

Paul, The reason I wanted a macro is because this will be repeitive process
on this set of data (possibly five to six different runs) and other similar
sets of alphanumeric data. . The project I am involved in is moving data
from my commercial accounting program into Excel, rearranging the data so
that it is exportable to a new accounting program. There is a tremendous
amount of "data cleansing" and the goal is to keep from manually entering the
data to the new accounting package. We currently use Excel outside of either
accounting programs but have not had the need to write code or use many
macros. Especially in reformatting data. Hopefully I will learn by these
examples. thanx in advance and I certainly appreciate your help.
"Paul Mathews" wrote:

Pat, I'm not certain if you need a macro but you can use regular Excel string
functions to accomplish the text parsing you need (you may need a macro if
you intend to perform this parsing action repeatedly with different data).
I'll provide the string function approach first. Let us know if you
absolutely need a macro and I or another party will provide the code.

First, move your original City,State,Zip data column to another column
location. Let's say you place it in column H with the first data item
occuring at H2 (i.e., header row is in row 1). Now, set cells E2, F2 and G2
as follows (exclude the double-quotes around the expressions):

E2: "=LEFT(H2,FIND(",",H2)-1)"
F2: "=MID(H2,FIND(",",H2)+1,2)"
G2: "=RIGHT(H2,5)"

So, for example, if cell H2 contains the text, "Manasquan,NJ,08736", then E2
will contain the text "Manasquan", F2 will contain "NJ", and G2 will contain
"08736". I'm presuming that you're using comma delimiters only (not comma
followed by space) and that all zip codes are represented in the usual five
numeric character format. Copy E2, F2, and G2 down as far as you need for
your data. If you want to delete the original unparsed data, be sure to copy
and paste-value the expressions in columns E, F and G. Hope this helps.

Paul

"pattlee" wrote:

Data is in Column G As City,State,Zip Need a Macro that will move City to Col
E and the State to Col F. and leave the Zip in the Col G.... Can't seem to
get the ofset parameter correct..or get rid of the comma between City,State
Would appreciate any help Regards Patt