A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

separate values from 1 cell to many rows



 
 
Thread Tools Display Modes
  #1  
Old February 7th, 2010, 06:25 PM posted to microsoft.public.excel.misc
bj 3233
external usenet poster
 
Posts: 2
Default 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
  #2  
Old February 7th, 2010, 06:33 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
  #3  
Old February 7th, 2010, 06:56 PM posted to microsoft.public.excel.misc
Russell Dawson
external usenet poster
 
Posts: 49
Default 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

  #4  
Old February 7th, 2010, 07:05 PM posted to microsoft.public.excel.misc
MichaelVB
external usenet poster
 
Posts: 2
Default 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

  #5  
Old February 7th, 2010, 07:27 PM posted to microsoft.public.excel.misc
Russell Dawson
external usenet poster
 
Posts: 49
Default 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

  #6  
Old February 7th, 2010, 07:46 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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


  #7  
Old June 2nd, 2010, 04:37 PM posted to microsoft.public.excel.misc
Sajjad Ahmed
external usenet poster
 
Posts: 1
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.