Formatting text cells to use in a formula

[marshybid]

Hi All,

I have a set of data that is delivered to me with a number of the cells containing the following information;

0 HRS 18 MIN (example of one cell)
5 DAYS 4 HRS 3 MIN (example of another cell)

These cells are formatted as general.

I need to be able to use the days, hours and minutes values as part of a formula, but I don't know how to remove the DAYS, HRS, MIN references and just use the values. I do need the values to be seen as Days, Hrs and Min in the formula. Help gratefully received.

Thanks,

Marshybid :doh:

[Bob Phillips]

Do it in stages

B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," MIN","")," HRS","")," DAYS","")
C1: =LEN(B1)-LEN(SUBSTITUTE(B1," ",""))
D1: =IF(C1=2,LEFT(B1,FIND(" ",B1)-1),
IF(C1>1,--MID(B1,FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1-2))+1,
FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1-1))-FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1-2))),0))+
TIME( IF(C1=1,LEFT(B1,FIND(" ",B1)-1),
IF(C1>0,--MID(B1,FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1-1))+1,
FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1))-FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1-1))),0)),
RIGHT(B1,LEN(B1)-FIND(CHAR(1),SUBSTITUTE(B1," ",CHAR(1),C1))),
0)

and format D1 as d hh:mm:ss

[marshybid]

Thanks xld, I'll try it out now.

Marshybid

[Bob Phillips]

BTW, I see you posted that other problem at OzGrid and got no (useful) replies.

Want to pick up what I gave you again (i.e. not going back to the start)?

[marshybid]

xld, I think I need to just post the full macro again and look at how the changes you posted affected the data. It seemed to place the dta in the wrong columns and was not filtering out the date info as expected. I will repost on the thread this weekend.

Don't get me wrong, I really appreciated the help, but I didn't want to keep coming back to you on the same thing :bow:

Have a good week end

Marshybid

[Bob Phillips]

Not berating you marshy, but you cannot believe my look of consternation when I saw you post that code which had the latest changes but just ditched all of those from the previous iterations. You have the right to take/discard what you wish, just as I have the right to respond or not. But it was the wrong move, trust me.

Post the workbook, and let's pick it up again. As I said it worked with the data I used, so let me know what your filter criteria are etc.