title: Convert String To Time Duration In Excel In Any Of The Different Formats
body: <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>