Parsing text in a cell (2000 SP3)

[gglmi]

I, too, am trying to parse text from a cell... I have 2 lines of text (separated by a manual line break ... ALT-ENTER). When running the Text to column wizard, what do I tell Excel to look for as the delimiter so the program will put each line into its own cell??

TIA,
gg

[LegareColeman]

Put a check mark next to Other. Put the cursor in the box, make sure Num Lock is ON, hold down the Alt key and type 010 on the numeric keypad (not on the number keys above the letters). Release Alt and click Finish.

[zeddy]

I don't think you can parse data separated with an Alt-Enter.

However, if your data is in column [A] starting in say cell [A2] perhaps you could try these formulas to extract the 2 lines of data into columns [b] and [C]:
in cell [B2]:
=LEFT(A2,FIND(CHAR(10),A2)-1)

in cell [C2]:
=MID(A2,FIND(CHAR(10),A2)+1,999)

..I'm sure you may get better answers from the experts!

zeddy

[gglmi]

It works!!! Many thanks; you have saved me hours of manual work. <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
gg

[MarkD]

If you want to use VBA to do this, here is a simple macro:

Sub TextToColumnsAltEnter()

ActiveSheet.Range("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=True, _
Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:=Chr$(10), FieldInfo:=Array(1, 2)

End Sub

In this example the text delimited with ALT-ENTER is located in Column A of the active worksheet. Use the OtherChar argument to specify delimiter, such as non-printable characters that can't be entered directly in the TextToColumns dialog.

HTH

[gglmi]

Many thanks! Actually using the ??ASCII code (ALT-010) for the line break as suggested by Legare Coleman worked in the Text To Columns dialog box.

gg