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

Have you tried DATA-TEXT TO COLUMNS?


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546855

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

Thank you for help.
I just tried it, but it did not solve the problem. It breaks the data with
blank or other characters such as comma. The data I have was written without
any consideration of usual rule. Some data have comma between street
address-city and city-state, but no comma between state-zip. And there are
blanks between number of street address and street name that I want it to be
in a cell. Thus I cannot convert it using blank or characters.
Do you have any other idea?

Charles


"mrice" wrote:


Have you tried DATA-TEXT TO COLUMNS?


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=546855

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

How about =SUBSTITUTE(B7," ","^") where B7 has your data - copy down
In column C

Then Copy - paste special Column C data to Column D

And then do a Text to column option on column D using the other category and
the ^


"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

Thanks mrice, Kevin, and Brad for your response.
After testing your recommendations and trying more methods, I finally gave
up using macro. Since all the address is in california and zip codes starting
with number 9, I used replace function to put comma before zip code and
state. Then also using replace with copying & pasting city name, I put comma
before city names, too. Then finally I used text to columns using comma as
delimiter to divide a cell to four cells. Well, it was more work than I hoped
for, but still was less than doing one by one.
Thanks again for giving me the idea.

Charles

"Brad" wrote:

How about =SUBSTITUTE(B7," ","^") where B7 has your data - copy down
In column C

Then Copy - paste special Column C data to Column D

And then do a Text to column option on column D using the other category and
the ^


"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