asked on

Excel Extract data from multiline cell

Hi All,

The software I use outputs the data like below in a single cell.

username 2/6/12 13:00 14:15 01:15 UAT, backup config, warranty cleanup and installation of KB/mouse for bob amongst other tasks.
username 2/6/12 10:15 12:00 01:45 UAT, backup config, warranty cleanup and installation of KB/mouse for bob amongst other tasks.
Total: 03:00

Open in new window

There could be quite a few lines in the cell so I am want to know if it is possible to extract the data into their own cells
e.g.[date ][time][Description] of each line into separate cells for each line? Then the total in its own cell.

I was able to get the total into its own cell by using

=MID(B6,FIND("Total:",B6)+6,6)

Open in new window

Maybe this picture would better explain it what I am after.

Looking forward to your help.

Thanks!

dlmille

Have you tried the Text-To-Columns feature? In Excel 2007+ its under the Data ribbon, Data Tools group. It looks like the format spacing is fixed, so this should do the trick quite well.

http://excelhints.com/2010/01/29/using-text-to-columns-to-separate-data/

Give it a shot.

See attached result.

Dave
textToColumnsResult.xlsx

dlmille

Ok. I see more clearly, now.

If your data in in D5, put this in a cell D11:G11

[D11]=RIGHT(D5,LEN(D5)-FIND("UAT",D5)+1)
[E11]=MID(D5,FIND("/",D5,FIND("/",D5)+1)+4,5)
[F11]=MID($D5,FIND(E11,$D5)+6,5)
[G11]=MID($D5,FIND(F11,$D5)+6,5)

and copy down.

You already did the total.

See attached.

Dave
splitCells.xls

Eirejp

ASKER

Hi Dlmille,

This is awesome thanks. The only problem is you had split the lines per cell. Actually all of the text including the total is a single cell.

Any suggestions.

Cheers,
Carl

dlmille

Will get back to you when I have pc access in a few hours

Dave

dlmille

updated - see yellow shaded area in attachment.

if data is in B18, then

[B24]=MID(B18,FIND("UAT",B18),FIND(CHAR(10),B18)-FIND("UAT",B18))
[B25]=MID(B18,FIND("UAT",B18,FIND(CHAR(10),B18)),FIND("Total",B18)-FIND("UAT",B18,FIND(CHAR(10),B18)))

That gets the text portion for the first and second line (notice the search starts after the line feed). The time portion follows the same approach.

See attached,

Dave
splitCells.xls

Eirejp

ASKER

Cheers Dave your awesome.

sorry one more question the description line can be anything so we cant use UAT as a start point for the fomula.

Sometime it would be like

greg [date] [time] [time] Install PC, adjusted monitor

dlmille

Sorry - I thought UAT was some type of time code, like GMT??

Eirejp

ASKER

Unfortunately not it was a abbreviation for user acceptance training which was part of the description.

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eirejp

ASKER

Great help thank you for all your help