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?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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,019
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,019

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,019
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.
 

Forum statistics

Threads
1,141,930
Messages
5,709,400
Members
421,635
Latest member
mehdi hannechi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top