Splitting up a cell which has Alt+Enter lines

Cranberries

New Member
Joined
Oct 11, 2006
Messages
5
Hi there

I have a spreadsheet with a cell containing a few lines in one cell and I would like to split the cell into many cells ie:

Currently the cell contains 3 rows in one single cell(as Alt + Enter was used to get them all in one cell)
100
200
300

Now what I'm trying to do is split up the contents of the above cell into 3 different cells

ie
100
200
300

all on different rows.

Is the above possibe?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
These formulas will separate the string into 3 cells.

=LEFT(A1,FIND(CHAR(10),A1)-1)

=MID(A1,FIND(CHAR(10),A1)+1,LEN(A1)-FIND("#",SUBSTITUTE(A1,CHAR(10),"#",2)))

=MID(A1,FIND("#",SUBSTITUTE(A1,CHAR(10),"#",2))+1,LEN(A1))

where A1 contains the original string
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Steve,

I'm not sure how you're gonna be able to use Text to Columns to do that. How are you getting the LF character input into the dialog box?<hr />
Cranberries,

Welcome to the board.

Put this UDF into a standard module<sup>1</sup>:
Code:
Public Function udfSplitOnLF(strInput As String) As Variant
    udfSplitOnLF = Split(strInput, vbLf)
End Function
Then array enter<sup>2</sup> the following into three cells. <ul>[*]If splitting into three columns: {=udfSplitOnLF(A1)}[*]If splitting into three rows: {=TRANSPOSE(udfSplitOnLF(A1))}[/list]<hr /><sup>1</sup>Assumes Excel 2002 or better. Not sure if SPLIT is in VBA in Excel 2000. Pretty sure it's not in XL97.
<sup>2</sup>To array enter a formula select all the cells into which the formula will be entered (three in this example) and after typing in the formula confirm it using the key combination Ctrl+Shift+Enter instead of just the Enter key.


HTH
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

How are you getting the LF character input into the dialog box?
Hi, Greg,

you can do this while holding ALT, type 10 (or maybe 13)

kind regards,
Erik
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

Erik,

I know that in general yes you can use Alt+ASCII code to pull up other characters. I frequently do it when typing Spanish (¿,¡,ñ,Ñ,á,é,í,ó,ú,«,»,ª,º and so forth).

But in this context of the Text-to-Columns dialog, does that work for you? Because it does not work for me. (Alt+Enter generates an ASCII 10, i.e. [=CODE(MID(A1,2,1)) returns 10]). But be that as it may, I cannot use Text to columns and use Alt+10 [in numeric keypad] and have it parse the Alt+Tab correctly.

Regards,
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Then you're doing it wrong, Truby, 'cos it works for me!

Seriously, I use Delimiter>Other and hold down Alt and type '010' from the keypad and it works perfectly.

Richard
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Erik,

I know that in general yes you can use Alt+ASCII code to pull up other characters. I frequently do it when typing Spanish (¿,¡,ñ,Ñ,á,é,í,ó,ú,«,»,ª,º and so forth).

But in this context of the Text-to-Columns dialog, does that work for you? Because it does not work for me. (Alt+Enter generates an ASCII 10, i.e. [=CODE(MID(A1,2,1)) returns 10]). But be that as it may, I cannot use Text to columns and use Alt+10 [in numeric keypad] and have it parse the Alt+Tab correctly.

Regards,
yes, Greg, it works for me

step by step what I did
copy the lines form above
100
200
300
enter cell with F2 and paste
result is cell with enlarged height
text to columns
delemiter: other
click in box
hold ALT
type 10 (numeric keypad)
done

I'm using the same technique to find-replace

best Greg-arts,
Erik

Office XP

EDIT: before I used "010" like Richard mentioned, but just discovered it works without "leading zero"
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Ah, Richard, that was it. I didn't prefix with the zero because I thought that it didn't make any difference. I thought the zero prefix meant to return Unicode character instead of ASCII and that for the lower characters (those below the space (32)) the maps were the same. Eg: compare the output of Alt+189 which returns the old ASCII box drawing character: to the output of Alt+0189 which returns the fraction character for one half: ½ Interesting that for me Alt+10 is not the same as Alt+010 but for Erik it is. I can only assume it's due to international settings for Windows or something.

Thanks again, Richard.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,953
Messages
5,545,145
Members
410,666
Latest member
Al3cs
Top