Results 1 to 2 of 2

Thread: Text to Columns Issue

  1. #1

    Text to Columns Issue



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi There
    Please see attached for a small sample of my problem file.
    Most cells have 3 lines of data and I want to get all three lines into an adjoining cell in a continuous one-line format.
    Secondarily, I need to break up the address into a mail-merge format, so the continuous address shows over 5 cells or so (address1, Address2, City, State, Postcode)
    I have seen some advice elsewhere which describes how to do a regular text to columns instruction but instead type '010' in the 'other' Delimiter box.
    Also, some advice about 'find & replace' function to replace the 'carriage returns' with a symbol and then use the symbol as a delimiter.
    If you try 'text to columns' in either of these scenarios on the ATTACHED data, when it gets to step 2 of the Wizard, only a fraction of the data shows, i.e. the first line.
    Can anyone help explain how to get the data in each cell in column A to be spread out over many cells on the same row, using Text to |Columns?

    Thanks
    Mick
    Attached Files Attached Files

  2. #2
    Conjurer Beamer's Avatar
    Join Date
    Nov 2014
    Posts
    109
    Articles
    0
    Excel Version
    365
    I couldn't get text to columns to work either, but there is a line feed and cr in between lines (chars 10 and 13), so I found this formula works, then you can copy and paste hard data afterwards.

    Cell B2: =LEFT(A2,SEARCH(CHAR(13),A2)-1)
    Cell C2: =MID(A2,LEN(B2)+3,SEARCH(CHAR(13),RIGHT(A2,LEN(A2)-SEARCH(CHAR(13),A2)-2)))
    Cell D2: =MID(A2,LEN(B2)+LEN(C2)+5,LEN(A2)-LEN(B2)-LEN(C2)-6)




    Edit: My original formula still left some of the line feeds in there...I've replaced them to make the data "cleaner"
    Attached Files Attached Files
    Last edited by Beamer; 2015-12-23 at 08:19 AM.

Posting Permissions

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