Using macro to copy a part of a cell content to next cell
 

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

Using macro to copy a part of a cell content to next cell
 

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

Using macro to copy a part of a cell content to next cell
 

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

Using macro to copy a part of a cell content to next cell
 

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

Using macro to copy a part of a cell content to next cell
 

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

Using macro to copy a part of a cell content to next cell
 

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

Using macro to copy a part of a cell content to next cell
 

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