getting the hour number out of string of characters

[bartvdg]

I have some excel data with shows up in this format:

PT1H11M47S

<tbody>
</tbody>

I would like to extract the number before the letter "H", in this example result needs to be 1

I can do this by using two formulas:
=TRIM(SUBSTITUTE(P16;"PT";"")) removes PT ---> 1H11M47S
=LEFT(Q16;VALUE(FIND("H";Q16;1))-1) shows the result ---> 1

I'm not succeeding in merging the 2 formulas, or maybe there is an easier way to do this?
Thanks!

 

[BenMiller]

What about

=MID(P16,3,FIND("H",P16)-3)

 

[admiral100]

Hi,

Try this (you should bring more examples...)

=MID(P16,FIND("PT",P16)+2,1)

 

[bartvdg]

Here are more examples and the desired outcome, your formula only brought one number

Time with Agent	Hours with Agent
PT1000H11M47S	1000
PT53H11M47S	53
PT1H11M47S	1
PT8M51S	0
PT33M27S	0

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[bartvdg]

thanks
How can I show zero when there is no H in the string?

 

[bartvdg]

Found it :-/
=IFERROR(MID(P11;3;FIND("H";P11)-3);0)

Thanks both for the quick reply!

 

[jtakw]

Hi,

I'd imagine you would want the formula results converted to Real Numbers for further math/comparisons, just add +0 :

=IFERROR(MID(P11;3;FIND("H";P11)-3)+0;0)