Convert duration in XXm, YYs format to time value

Question

In Microsot Excel, I have string data that represents duration of an event:

duration
40s
5m, 40s
59m, 59s

I need to convert the values to time (in adjacent column) so that I can format and sort the data properly. For example:

duration	seconds	formatted
40s	40	00:00:40
5m, 40s	340	00:05:40
59m, 59s	3599	00:59:59

Answer 1

Two alternative options, you could try:

---

• Formula used in cell B2

=SUM(IFERROR(MID("0"&A2,SEARCH({"d","h","m","s"},A2)-1,2),0)*{"1","1:0","0:1","0:0:1"})*86400

---

Or,

• Formula used in cell C2

=SUM(IFERROR(MID(0&A2,SEARCH({"d","h","m","s"},0&A2)-2,2),{0,0,0,0})*{86400,3600,60,1})

---

And in D2

=B2/24/60/60

---

Format cells as [h]:mm:ss;@

---