Carriage Return By Replace() function
 

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

Carriage Return By Replace() function
 

Try this:

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

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

Formatting the cells to wrap text: FormatCellsAlignment tabWrap textOK
Adjust the column width
Adjust the row height

--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
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

Carriage Return By Replace() function
 

Hi Faraz,

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

HTH
Martin


"FARAZ QURESHI" wrote in message
...
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

Carriage Return By Replace() function
 

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

(Easier to do and easier to explain <bg)

MartinW wrote:

Hi Faraz,

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

HTH
Martin

"FARAZ QURESHI" wrote in message
...
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


--

Dave Peterson

Carriage Return By Replace() function
 

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

<g
Martin

"Dave Peterson" wrote in message
...
You could also use ctrl-j instead of the alt-0010 (from the numeric
keypad).

(Easier to do and easier to explain <bg)

MartinW wrote:

Hi Faraz,

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

HTH
Martin

"FARAZ QURESHI" wrote in message
...
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


--

Dave Peterson

Carriage Return By Replace() function
 

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

"MartinW" wrote:

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

<g
Martin

"Dave Peterson" wrote in message
...
You could also use ctrl-j instead of the alt-0010 (from the numeric
keypad).

(Easier to do and easier to explain <bg)

MartinW wrote:

Hi Faraz,

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

HTH
Martin

"FARAZ QURESHI" wrote in message
...
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


--

Dave Peterson

Carriage Return By Replace() function
 

Not from me.

FARAZ QURESHI wrote:

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

"MartinW" wrote:

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

<g
Martin

"Dave Peterson" wrote in message
...
You could also use ctrl-j instead of the alt-0010 (from the numeric
keypad).

(Easier to do and easier to explain <bg)

MartinW wrote:

Hi Faraz,

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

HTH
Martin

"FARAZ QURESHI" wrote in message
...
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


--

Dave Peterson




--

Dave Peterson