split text into separate cells

[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

 

[Guest]

try:

CHAR(10) for "alt_enter"

 

[Sebation.G]

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

try Data>Split

Regards,

Sebation.G

 

[PCLIVE]

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

 

[Guest]

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

 

[PCLIVE]

Very nice! I learn something new all the time.

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

 

[Dave Peterson]

And instead of using alt-0010, you could use ctrl-j

(even easier to describe and do!)

 

[Gord Dibben]

Data>Text to columns>Limited by other hit CTRL + j and finish.


Gord Dibben MS Excel MVP