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

[Charles]

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

[mrice]

Have you tried DATA-TEXT TO COLUMNS?

[Kevin B]

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

[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
>
>

[Charles]

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

[Brad]

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

[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