split text into separate cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of book titles that came over all in one cell. I would like to
separate them into individual cells, one title per cell. They are separated
into lines using the "alt-enter" command to advance by one line when entering
text in a cell. If I can find a way for excel to recognize the "alt-enter" in
the text then i think i can do it with the find command. Any ideas?

Thanks in advance.

John T
 
I have a list of book titles that came over all in one cell. I would liketo
separate them into individual cells, one title per cell. They are separated
into lines using the "alt-enter" command to advance by one line when entering
text in a cell. If I can find a way for excel to recognize the "alt-enter" in
the text then i think i can do it with the find command. Any ideas?

Thanks in advance.

John T
Click to expand...

try Data>Split

Regards,

Sebation.G
 
One way:

You could first use some code to replace the Alt+Enter characters with
commas.

Sub test()
Range("A1").Value = WorksheetFunction.Substitute(Range("A1").Value, Chr(10),
",")
End Sub

Then use Text to Columns from the Data menu.

HTH,
Paul
 
You can use Text to Columns:
Pull-down:
Data > Text to Columns..
check Delimited
clear all checkboxs except Other
click in the Other entry box and while holding down the ALT key type
010
 
Very nice! I learn something new all the time.


Gary''s Student said:
You can use Text to Columns:
Pull-down:
Data > Text to Columns..
check Delimited
clear all checkboxs except Other
click in the Other entry box and while holding down the ALT key type
010
Click to expand...
 
And instead of using alt-0010, you could use ctrl-j

(even easier to describe and do!)
 
Data>Text to columns>Limited by other hit CTRL + j and finish.


Gord Dibben MS Excel MVP
 
Back
Top