separate values from 1 cell to many rows

[bj 3233]

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]

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.)

 

[MichaelVB]

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.

 

[Gord Dibben]

Select the cell and Edit>Replace

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 Edit>Paste.

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

 

[Sajjad Ahmed]

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:

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

Do you have MSWord?
Do you have MSWord

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

Select the replace with bo
Hit Mor
choose Specia
Choose Paragraph mar
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

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 i
the ribbon select the Data tab then In Data tools - text to columns. Ensur
you have only selected the first column with your data
Original data type - delimited - nex
Delimiters - other - input the > and ensure all other boxes are unchecked
nex
Data preview - select each col and make text - finis
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 Dawso
Excel studen

:

If the email address is before the > symbol: Cell A1 includes email
If the email address is before the > symbol: Cell A1 includes email addres
=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

:

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

Russell Dawso
Excel studen

:

Select the cell and Edit>ReplaceWhat: >With: CTRL + jReplace all.
Select the cell and Edit>Replac

What:

With: CTRL +

Replace all

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

Select enough cells below to hold all the addresses then Edit>Paste

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

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/tutorial...45-8b37cb7f3186/wpf-report-engine-part-4.aspx