Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing text in a cell (2000 SP3)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing text in a cell (2000 SP3)

    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.
    Legare Coleman

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Parsing text in a cell (2000 SP3)

    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

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing text in a cell (2000 SP3)

    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

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Parsing text in a cell (2000 SP3)

    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

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Virginia, USA, Virginia, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing text in a cell (2000 SP3)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •