Carriage Return By Replace() function

[FARAZ QURESHI]

I have a data like:
A1: ABCDEFG, HIJK, LMNOPQRSTUV, WXYZ
A2: ABC, DEFG, HIJK, LMNOPQRSTUV, WXYZ
A3: ABCDEFGHIJK, LMNOPQRSTUV, WXYZ

Shall appreciate a formula using Replace and Char(10) function in cells B1,
B2, B3 or ANY other way so as to present the data as:

B1: ABCDEFG,
HIJK,
LMNOPQRSTUV,
WXYZ
B2: ABC,
DEFG,
HIJK,
LMNOPQRSTUV,
WXYZ
B3: ABCDEFGHIJK,
LMNOPQRSTUV,
WXYZ

 

[T. Valko]

Try this:

=SUBSTITUTE(A1," ",CHAR(10))

You'll have to "clean it up" a little by:

Formatting the cells to wrap text: Format>Cells>Alignment tab>Wrap text>OK
Adjust the column width
Adjust the row height

 

[MartinW]

Hi Faraz,

Just do an Edit>Replace in place
Replace What: space
Replace With: Alt+0010
Click replace All

HTH
Martin

 

[Dave Peterson]

You could also use ctrl-j instead of the alt-0010 (from the numeric keypad).

(Easier to do and easier to explain <bg>)

 

[MartinW]

See that 99, the old Ctrl+J trick, that's the third time I've missed it this
year!

<g>
Martin

 

[FARAZ QURESHI]

XCLent!!
Any other further usable combinations of ctrl+... ?

See that 99, the old Ctrl+J trick, that's the third time I've missed it this
year!

<g>
Martin

 

[Dave Peterson]

Not from me.

XCLent!!
Any other further usable combinations of ctrl+... ?