separate values from 1 cell to many rows
 

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

separate values from 1 cell to many rows
 

Do you have MSWord?

If yes, copy that list into a new document in MSWord.
While in MSWord
Hit ctrl-a to select all the text
hit ctrl-h (edit|Replace in Word2003 menus)
Find what: (the greater than symbol)

Select the replace with box
Hit More
choose Special
Choose Paragraph mark
Then replace all.

Now select that list and copy|paste into excel.
(or edit|paste special|text in excel if you don't want the formatting.)




bj 3233 wrote:

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

--

Dave Peterson

separate values from 1 cell to many rows
 

It seems that you have the addresses all in one row.
If that is correct why not use the "Text to columns" function. In 2007 in
the ribbon select the Data tab then In Data tools - text to columns. Ensure
you have only selected the first column with your data.
Original data type - delimited - next
Delimiters - other - input the and ensure all other boxes are unchecked -
next
Data preview - select each col and make text - finish
this should now have the result of each email address in a column of it's own.
Now copy and paste special - transpose to create the addresses in rows.

Was this post helpful to you? Please click yes.

--
Russell Dawson
Excel student


"bj 3233" wrote:

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

separate values from 1 cell to many rows
 

If the email address is before the symbol: Cell A1 includes email address
=left(a1,find("",a1,1)-1)

If the email address is after the symbol:
=right(a1,len(a1)-find("",a1,1))

Copy one of these formulas in a column and copy down your list.


"bj 3233" wrote:

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

separate values from 1 cell to many rows
 

Please let us know what solution you choose. We're all learning and I am
keen to learn as much as I can.

Russell Dawson
Excel student


"bj 3233" wrote:

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

separate values from 1 cell to many rows
 

Select the cell and EditReplace

What:

With: CTRL + j

Replace all.

With cell still selected hit F2 then select all text in the formula bar and
Copy.

Select enough cells below to hold all the addresses then EditPaste.

Ignore the warning about "not same size" and hit OK

Your addresses are now in individual cells in the column.


Gord Dibben MS Excel MVP

On Sun, 7 Feb 2010 09:25:01 -0800, bj 3233
wrote:

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

Mrs Excel
 

i want to learn more in excel field




bj 3233 wrote:

separate values from 1 cell to many rows
07-Feb-10

I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

Previous Posts In This Thread:

On Sunday, February 07, 2010 12:25 PM
bj 3233 wrote:

separate values from 1 cell to many rows
I have a list of email address that are separated by the symbol.
I want to separte each address to its own row

On Sunday, February 07, 2010 12:33 PM
Dave Peterson wrote:

Do you have MSWord?
Do you have MSWord?

If yes, copy that list into a new document in MSWord.
While in MSWord
Hit ctrl-a to select all the text
hit ctrl-h (edit|Replace in Word2003 menus)
Find what: (the greater than symbol)

Select the replace with box
Hit More
choose Special
Choose Paragraph mark
Then replace all.

Now select that list and copy|paste into excel.
(or edit|paste special|text in excel if you do not want the formatting.)




bj 3233 wrote:

--

Dave Peterson

On Sunday, February 07, 2010 12:56 PM
Russell Dawson wrote:

It seems that you have the addresses all in one row.
It seems that you have the addresses all in one row.
If that is correct why not use the "Text to columns" function. In 2007 in
the ribbon select the Data tab then In Data tools - text to columns. Ensure
you have only selected the first column with your data.
Original data type - delimited - next
Delimiters - other - input the and ensure all other boxes are unchecked -
next
Data preview - select each col and make text - finish
this should now have the result of each email address in a column of it is own.
Now copy and paste special - transpose to create the addresses in rows.

Was this post helpful to you? Please click yes.

--
Russell Dawson
Excel student


"bj 3233" wrote:

On Sunday, February 07, 2010 1:05 PM
MichaelVB wrote:

If the email address is before the symbol: Cell A1 includes email
If the email address is before the symbol: Cell A1 includes email address
=left(a1,find("",a1,1)-1)

If the email address is after the symbol:
=right(a1,len(a1)-find("",a1,1))

Copy one of these formulas in a column and copy down your list.


"bj 3233" wrote:

On Sunday, February 07, 2010 1:27 PM
Russell Dawson wrote:

Please let us know what solution you choose.
Please let us know what solution you choose. We're all learning and I am
keen to learn as much as I can.

Russell Dawson
Excel student


"bj 3233" wrote:

On Sunday, February 07, 2010 1:46 PM
Gord Dibben wrote:

Select the cell and EditReplaceWhat: With: CTRL + jReplace all.
Select the cell and EditReplace

What:

With: CTRL + j

Replace all.

With cell still selected hit F2 then select all text in the formula bar and
Copy.

Select enough cells below to hold all the addresses then EditPaste.

Ignore the warning about "not same size" and hit OK

Your addresses are now in individual cells in the column.


Gord Dibben MS Excel MVP


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 4
http://www.eggheadcafe.com/tutorials...ne-part-4.aspx