Thanks Kevin.
But, as I wrote in reply to mrice, my data was not written using comma as a
delimiter. It was written as you'd write in an envelope: street address,
(sometimes no comma) city, state zipcode. There are no comma between
state-zip. If city names were only one word, I would be able to use
text-to-columns function. Do I have to put comma where necessary and use your
code? Well, I'd wait and explore my option before I decide to do that.
Thanks anyway.

Charles


"Kevin B" wrote:

The following macro splits data using a comma as a delimiter. The macro
assumes that the data resides in Column A and starts in row 1.

Sub ParseAddress()

Dim strVal As String
Dim lngRow As Long
Dim intCol As Integer
Dim varArray As Variant
Dim varItems As Variant

Range("C1").Select
strVal = ActiveCell.Offset(lngRow, -2).Value

Do Until strVal = ""
varArray = Split(strVal)
varItems = varArray
For Each varItems In varArray
ActiveCell.Offset(lngRow, intCol).Value = varItems
intCol = intCol + 1
Next varItems
lngRow = lngRow + 1
intCol = 0
strVal = ActiveCell.Offset(lngRow, -2)
Loop

End Sub

Perhaps this will get you pointed in the proper direction.
--
Kevin Backmann


"Charles" wrote:

I have lists of street address, city, state and zip code written in a cell.
I'd like to put those in different cells, like city in a street address in a
cell, city in a cell, so on...
There are about 500 hundred of them, and I don't want to do that one by one.
I thought of using macro and tried without success.
It seems to be working, but it failed to copy and paste new data. I mean,
instead of copying and pasting the zip code from the cell selected, it keeps
pasting the first one.
Please help me.

Charles