Convert Unconventional D:H:M Counter to Total Minutes

[jkerekgyarto]

I need to convert these cells that contain elapsed time in the format shown, to total minutes.

The data in the cell is texts and numbers – there is a space on both sides of each number.

The formula is a Value function with a nested Find – it’s not working for me, however.

=VALUE(LEFT(A2,FIND("D",A2)-2))*24*60 + VALUE(LEFT(A2,FIND("D",A2)-3))*24*60 + VALUE(MID(A2,FIND(":",A2)+2,FIND("H",A2)-2))*60 + VALUE(LEFT(A2,FIND("M",A2)-2))

The position of the characters in the cells is not static – see the difference between A2 and A3.

Image of cells in spreadsheet is uploaded.

Any help is very appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you have the LET function in your Excel 365?

21 01 06.xlsm
	A	B
1	Status	Total Minutes
2	DOWN for 7 D : 13 H : 8 M	10868
3	Sector for 12 D : 2 H : 16 M	17416
DHM

Cell Formulas
Range	Formula
B2:B3	B2	=LET(lng,RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),160),LEFT(lng,20)*1440+MID(lng,60,20)*60+MID(lng,120,20))