getting the hour number out of string of characters

[bartvdg]

I have some excel data with shows up in this format:
[TABLE="width: 133"]
<tbody>[TR]
[TD="class: xl65, width: 133"]PT1H11M47S[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 266"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Time with Agent[/TD]
[TD]Hours with Agent[/TD]
[/TR]
[TR]
[TD]PT1000H11M47S[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]PT53H11M47S[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]PT1H11M47S[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]PT8M51S[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]PT33M27S[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)