Splitting out multiple lines of data contained in a single cell

[The_Lingster]

Hi All,

I've been using this site for a while and found it very useful.

I'm struggling with a problem at the moment, basically I've had a lot of data sent to me to work from. The problem I've got is that a majority of the data that I need to analyse is contained in a single cell on multiple lines, by using ALT+RETURN in the cell to create a new line.

I've tried numerous options, I managed to pull out the date installed by using:

=IF((FIND("DATE INSTALLED",Q22,1))>0,(MID(Q22,((FIND("DATE INSTALLED",Q22,1))+15),6)),"")

This is possible as the year is always 4 characters long.


I've also been through and tried using Text to Columns, but there's no consistent delimiters etc, and also the data against each of the headings within the cells are of different length.

Does any one know of a way of using something like Text to Columns, and it puts each new line in the cell into a new column?

Thanks in advance, I hope someone can solve this - as I don't want to do it manually as there's 6000+ cells to look at.





Example below, all this data is contained on multiple lines in the same cell.

-------------------------------------------

Located in False Ceiling above Cleaners Room *******

ADDITIONAL DETAILS


LOCAL I.D. CLEANERS ROOM MAIN ******* SUPPLY AHU 1
AREA SERVED MAIN ******* FRYER EXTRACT
MAKE VENT AXIA
MODEL
SR NO.


DATE INSTALLED 1995


MOTOR DETAILS

LOCAL NO.
DRIVING FAN
HP./KW
MAKERS ABB MOTORS
MAKERS SR. NO.
TYPE
FREQUENCY 50 HZ
PHASES 3
F.L.CURRENT
VOLTS 415
SPEED
LUBRICANT SEALED
PULLEY SIZE
BELT SIZE/QTY

FILTERS


TYPE SIZE QTY PART NO.

PANEL FILTER 496X372X47 4 PT10201502


DUCT CLEANED SEPT99


---------------------------------------------

 

[sheetspread]

Try Andrew's suggestion here:

http://www.mrexcel.com/forum/excel-...nter-entries-single-cell-ms-excel-2010-a.html

 

[jak82]

Have you tried pulling it into power query and working on it there?

 

[Marcelo Branco]

Maybe this can be helpful
Split Data into Several Columns Based on Carriage Returns – Bacon Bits

M.