This is really ugly but it works on the format types listed below.

2d 17h 35m
17h 35m
2d
17h
25m

All on one line:

=IF(COUNT(SEARCH({"d","h","m"},A1))=3,LEFT(A1,FIND ("d",A1)-1)
+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1)
+1,20),"h ",":"),"m",""),IF(COUNT(SEARCH({"h","m"},A1))= 2,
--SUBSTITUTE(SUBSTITUTE(A1,"h ",":"),"m",""),
IF(COUNT(FIND("d",A1)),--SUBSTITUTE(A1,"d",""),
IF(COUNT(FIND("h",A1)),TIME(SUBSTITUTE(A1,"h",""), ,),
TIME(0,SUBSTITUTE(A1,"m",""),)))))

Format the result as CUSTOM d:hh:mm

Based on your samples returns:

0:00:09
2:17:35
0:06:19
0:12:21
0:00:28

A regular expressions solution would be much shorter but I don't know enough
about it to offer a solution.


--
Biff
Microsoft Excel MVP


"Keyrookie" wrote in message
...

I am trying to extract numbers from a string(s). The challenge for me
is the string is never constant so the RIGHT or LEFT functions will not
work for every situation. Here is an example of the string(s):

9m
2d 17h 35m
6h 19m
12h 21m
28m

For the LEFT/RIGHT functions to work I need the formula to return
this:

0d 00h 09m
2d 17h 35m
0d 06h 19m
0d 12h 21m
0d 00h 28m

My desired end result is this so I can add the time to NOW() and know
exactly what day/time a deadline ends:
:9
2:17:35 (I multiply the day number by 24 to get hours for time
addition)
6:19
12:21
:28


Thanks in advance for the help,

Keyrookie




--
Keyrookie