It worked ok for me on all the samples you posted.

Try Ron's UDF. That formula is real ugly!

--
Biff
Microsoft Excel MVP


"Keyrookie" wrote in message
...

Biff,

The formula worked great except for when the cell only had XXh XXm.
When there was anything to the left of XXh it returned a #FASLE! error.
Example:

17h 35m (worked fine)
2d (worked fine)
2d 17h 35m (returned error)

Thanks for your help,

Keyrookie



T. Valko;565313 Wrote:
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 -




--
Keyrookie