Thanks Paul Alos have a solution from Ron R . You have no idea how much this
means to us newbies.... will try both and be back with the kudos. for
both..... thanks

"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