Convert a Column with Text and Numbers to a Duration Cell in h:mm

B1 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("min",IF(ISERROR(FIND("hr",C1,1)),"0 hr, " &A1,A1))),IF(ISERROR(FIND("hr",C1,1)),"0 hr, " &A1,A1)&", 0 min",IF(ISERROR(FIND("hr",C1,1)),"0 hr, " &A1,A1)),"min",""),"s","")," hr, ",":")*1

Format custom hh:mm

Sorrry, sucuri firewall did not let me write the code, so find it in file attach.

OK, so here it is:

=TIMEVALUE(IF(IFERROR(FIND(":",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@String]," ",""),"min",""),"hr",""),"s",""),",",":")),0)=0,"0:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@String]," ",""),"min",""),"hr",""),"s",""),",",":"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@String]," ",""),"min",""),"hr",""),"s",""),",",":")))

Yeah, right!

See the attached spreadsheet to understand how I got there.

Colum A is String. This is the string as copied from your post.

Column B is String 1 which basically takes out min and mins, hr and hrs and substitutes a colon for the comma so the resulting string looks almost like a string for time.

Column C is String 2 which is either zero if string 1 does not have a colon or zero or some other number if String 1 does have a colon. The find command is one way to test if a string has a certain substring.

Column D is String 3 the formula here puts "0:" in front of String 1 when it needs it (in other words only minutes are in the original string).

Column E is Time which is the TIMEVALUE of string 3.

Colum F is the same formula as column E except that the pieces are successively substituted back until everything is expressed only in terms of Column A (The original string)

Try

=SUMPRODUCT(TEXT(MID(0&A2,FIND({"h","m"},0&A2&"hm")-2,2),"0;;;\0")/{24,1440})

While I like a few of the solutions - this is likely what I would utilize as a ripper value off of text... This assumes that you are consistent in formatting if there is no hour then just the minute is placed and if there is an hour it is written followed by a comma... this comma is the guide to the formula below... with or without it dictates if there are hours to be evaluated... so if your format is that in which you shared then this should work great.

Please Login or Register  to view this content.

Thanks mehmetcik, but I'm getting this instead of the desired output:

00:05
00:01
00:01
00:03
00:01
00:06

--

Thanks bebo021999, your formula works like a charm..

Here is it again:

=MID("00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m",IFERROR(SEARCH("hr","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m",SEARCH("hr","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m")+1),SEARCH("hr","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m"))-2,2)/24+MID("00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m",SEARCH("m","00hr00"&SUBSTITUTE(SUBSTITUTE(A1," ",""),",","00")&"000m")-3,3)/1440

--

Thanks dflak for the detailed reply.
Close enough, but getting this instead of the desired output:

0:05
0:01
1:45
0:03
0:01
6:01

(4 and 5 are wrong, as they're showing as minutes.)


--

Thanks Bo_Ry, close enough but the 3rd sample is giving 1:05 instead of 1:45

If your format stays like your example... this should be golden for you...

Please Login or Register  to view this content.

Try again

=SUMPRODUCT(TEXT(MID(0&A2,FIND({"h","m"},0&A2&"hm")-3,3),"0;;;\0")/{24,1440})