Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Excel 2003: Text to Columns: Cell contains line break

Latest post Tue, Jan 20 2015 5:06 AM by krishnap2w2. 4 replies.
  • Wed, Mar 14 2012 9:36 AM

    • west1028
    • Not Ranked
    • Joined on Wed, Mar 14 2012
    • Posts 1
    • Points 53

    Excel 2003: Text to Columns: Cell contains line break

    I have a worksheet that contains a column of addresses.  For example in cell A1 the information appears as below with a line break separating each line of text.

    John Smith
    ABC Company
    123 Main Street
    City, State  00000

    I need to have the data appear in column format to be able to export the data as a .csv file for import into another application.  I tried Text to Column but in only provided the first line of data (e.g., John Smith).  Any help would be appreciated.  Thanks.


    • Post Points: 53
  • Wed, Mar 14 2012 6:40 PM In reply to

    • njs220
    • Top 150 Contributor
    • Joined on Fri, May 20 2011
    • Posts 7
    • Points 115

    Re: Excel 2003: Text to Columns: Cell contains line break

    Try this

    Step 1) select the column

    Step 2) go into text to column

    Step 3) tick other and in the box type 0010 while Holding the Alt key. (it looks as if youve typed nothing, dont worry)

    Step 4) hit next and finish


    The reason this works is that 0010 is the ASCII code for a line break. (note that the numbers must be typed on a numberpad).

    You can also do a find and replace for Alt-0010 and then do the text to column.

    Hope that helps.


    • Post Points: 21
  • Thu, Mar 15 2012 6:11 AM In reply to

    • oldchippy
    • Top 50 Contributor
    • Joined on Thu, Feb 28 2008
    • Posts 32
    • Points 495

    Re: Excel 2003: Text to Columns: Cell contains line break


    Another option, if you have a column of names and addresses, each taking 5 rows is to put this formula in B1


    Then select B1:B5 and autofill down

    Select column B, copy and paste values

    Delete column A

    Sort column B to filter out the blank rows

    Then to Text to columns using the "*" as the delimiter

    • Post Points: 5
  • Wed, May 16 2012 10:49 AM In reply to

    • cnpriyanka
    • Not Ranked
    • Joined on Wed, May 16 2012
    • Posts 1
    • Points 5

    Re: Excel 2003: Text to Columns: Cell contains line break



    I have a data like below ( all in single cell) but I want first line in seperate column and second line in seperate column.I tired using space but it didint worked, even i used above mentioned formula through delimiter. But it is not working. please can any one help me in resolving this.

    data as

    UT MD Anderson Cancer Center 1515 Holcombe Blvd Unit 428 Houston TX 77030

    Many thanks in advcane!



    Filed under:
    • Post Points: 5
  • Tue, Jan 20 2015 5:06 AM In reply to

    Re: Excel 2003: Text to Columns: Cell contains line break

    How to Split Text into Different Columns?


    A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.


    Following are the steps to split data into different columns using ‘Text to columns’ :


    1 Select the range of data you need to work at.

    2 Select Data from menu and select Text to Columns in the Data Tools group.

    3 Check Delimited in the dialog box (1 of 3) and click Next.

    In Step 2 of 3, check the box related to the specific separator in the information.

    If the separator is tab, check tab or if the separator

    is semicolon, check semicolon and so on.

    If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:

    4 Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.

    5 Enter the Destination (C3) to paste the data after splitting and Click Finish.

    6 Click Ok in the dialogue box.

    Excel Consultants

    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.