Comma delimited text to columns
 

Im not a programmer so can someone help me with code for the problem listed below.

I have a column of addresses in Excel. Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first comma and second comma (individually) into their own columns. Then I need it deleted from the original column. I start with one column and end up with three columns. Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address city state zip

Thanks in advance.

Comma delimited text to columns
 

select column A (columns B to D should be blank)

do data = text to columns and in the wizard choose delimited and then
select the comma as delimiter on the next dialog.

--
Regards,
Tom Ogilvy

"Ann Vlna" <Ann wrote in message
...
I'm not a programmer so can someone help me with code for the problem
listed below.

I have a column of addresses in Excel. Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first comma
and second comma (individually) into their own columns. Then I need it
deleted from the original column. I start with one column and end up with
three columns. Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address city state zip

Thanks in advance.

Comma delimited text to columns
 

Ann,
Did you use Text to Columns under Data menu?
If you have to do it repeatedly then you can use a code
else you can adjust it manually.
Cecil

"Ann Vlna" <Ann wrote in message
...
I'm not a programmer so can someone help me with code for the problem
listed below.

I have a column of addresses in Excel. Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first comma
and second comma (individually) into their own columns. Then I need it
deleted from the original column. I start with one column and end up with
three columns. Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address city state zip

Thanks in advance.

Comma delimited text to columns
 

Ann,
Ok, copy about 30 lines of original records some with suite no.and some
without to newsgroup massage and Post back.
Cecil

You already got one from Tom Ogilvy and it will do the job for you

"Ann Vlna" wrote in message
...
I have to do this repeatly for about 150,000 records each time. If I use
the text to columns some records have four columns and some three. They
also don't fall in the correct column and have to be moved around. Also, if
there is an address and suite no. they have to be put back together.
Thanks, though.

"Cecilkumara Fernando" wrote:

Ann,
Did you use Text to Columns under Data menu?
If you have to do it repeatedly then you can use a code
else you can adjust it manually.
Cecil

"Ann Vlna" <Ann wrote in message
...
I'm not a programmer so can someone help me with code for the problem
listed below.

I have a column of addresses in Excel. Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first
comma
and second comma (individually) into their own columns. Then I need it
deleted from the original column. I start with one column and end up
with
three columns. Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address city state zip

Thanks in advance.

Comma delimited text to columns
 

Cecil, I received Tom's code and it did exactly what I wanted. I just wanted to let you know I appreciate you taking the time to help me.

"Cecilkumara Fernando" wrote:

Ann,
Ok, copy about 30 lines of original records some with suite no.and some
without to newsgroup massage and Post back.
Cecil

You already got one from Tom Ogilvy and it will do the job for you

"Ann Vlna" wrote in message
...
I have to do this repeatly for about 150,000 records each time. If I use
the text to columns some records have four columns and some three. They
also don't fall in the correct column and have to be moved around. Also, if
there is an address and suite no. they have to be put back together.
Thanks, though.

"Cecilkumara Fernando" wrote:

Ann,
Did you use Text to Columns under Data menu?
If you have to do it repeatedly then you can use a code
else you can adjust it manually.
Cecil

"Ann Vlna" <Ann wrote in message
...
I'm not a programmer so can someone help me with code for the problem
listed below.

I have a column of addresses in Excel. Example of original column is:

Column A
Street address, suite no., city, state zip
Street address, city, state zip

I need to start at the right and extract the data up to the first
comma
and second comma (individually) into their own columns. Then I need it
deleted from the original column. I start with one column and end up
with
three columns. Examples of new columns a

Column A Column B Column C
Street address, suite no. city state zip
Street address city state zip

Thanks in advance.