Text to Columns Issue

Mick C

New member
Joined
Dec 22, 2015
Messages
1
Reaction score
0
Points
0
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
 

Attachments

  • Text to Columns Query.xlsx
    8.1 KB · Views: 7
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"
 

Attachments

  • Text to Columns Query2.xlsx
    10.1 KB · Views: 4
Last edited:
Back
Top