Splitting up a cell which has Alt+Enter lines

[Cranberries]

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?

 

[SteveO59L]

Try Data, Text to Columns, the Transpose

 

[NBVC]

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]

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¹:

Public Function udfSplitOnLF(strInput As String) As Variant
    udfSplitOnLF = Split(strInput, vbLf)
End Function

Then array enter² the following into three cells. <ul>[*]If splitting into three columns: {=udfSplitOnLF(A1)}[*]If splitting into three rows: {=TRANSPOSE(udfSplitOnLF(A1))}[/list]<hr />¹Assumes Excel 2002 or better. Not sure if SPLIT is in VBA in Excel 2000. Pretty sure it's not in XL97.
²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]

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

 

[SteveO59L]

Cheat and use fixed width

 

[Greg Truby]

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]

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]

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]

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.