| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to find replace text or symbol with carriage return | New Users to Excel | |||
| Find and replace a space with carriage return in excel | Excel Discussion (Misc queries) | |||
| How to remove or replace a carriage return character in a cell? | Excel Discussion (Misc queries) | |||
| Search and replace with Carriage return | Excel Discussion (Misc queries) | |||
| Find/Replace carriage return & line feed characters in Excel. | Excel Worksheet Functions | |||
Hybrid Mode
