Convert String To Time Duration In Excel In Any Of The Different Formats
<p><a href="https://i.stack.imgur.com/WqNHj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WqNHj.png" alt="Previous asked question"></a></p>

<p>I have asked a similar question on <a href="https://stackoverflow.com/questions/36346973/convert-string-to-time-duration-in-excel">here</a>, and the answer is accurate and efficient, but it only work when the input is  </p>

<blockquote>
  <p>`
  1 h 1 m 1 s</p>
</blockquote>

<p>format. If other formats, it will cause error.</p>

<p>The solution from the question</p>

<blockquote>
  <p><code>= VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5," h ",":")," m ",":")," s",""))</code></p>
</blockquote>

<h2>-------------------------------------------------------------------------------------------------------</h2>

<p><strong>My question as below:</strong></p>

<p>May I know the way to convert the following inputs to the expected output?</p>

<ol>
<li><strong>1 h 48 m 5 s</strong>  to <strong>1:48:05</strong></li>
<li><strong>48 m 5 s</strong> to <strong>0:48:05</strong></li>
<li><strong>5 s</strong> to <strong>0:0:05</strong></li>
<li><strong>1 h 5 s</strong> to <strong>1:0:5</strong></li>
<li>Other combination of <strong>h m s</strong> to <strong>0:0:0</strong> format</li>
</ol>

<p>Thanks in advance</p>

<hr>

<p>Based on the two formulaes</p>

<blockquote>
  <p>= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(SEARCH("m",S32)),IF(ISNUMBER(SEARCH("h",S32)),"","0h"),"0h0m")&amp;S32,"
  ",""),"m",":"),"h",":"),"s","")*1</p>
</blockquote>

<p>and</p>

<blockquote>
  <p>=(IF(ISERROR(SEARCH("h",A1)),"0:","")&amp;IF(ISERROR(SEARCH("m",A1)),"0:","")&amp;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m",":"),"s",""))+0</p>
</blockquote>

<p>If the input is it </p>

<blockquote>
  <p><code>1 h 5 s</code></p>
</blockquote>

<p>it causes error.</p>

<p>The result should follow the test cases as below</p>

<p><a href="https://i.stack.imgur.com/hJs1M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hJs1M.png" alt="Test Case"></a></p>