CODEWITHSUNDEEP
excelformula
10101
10101

Reputation: 2402

Excel formula to SUBSTITUTE value with specific parameters

I have formula to convert text "2 hours 43 mins" to "2,43". Sometimes value is "1 hour 43 mins" and my current solution does not work in this case. How to make it work in both cases "2 hours 43 mins" and "1 hour 43 mins"? I have tried to use OR with no success.

This is my current formula:

=SUBSTITUTE(SUBSTITUTE(Q68;"mins";"");" hours ";",")

I have tried:

=SUBSTITUTE(SUBSTITUTE(Q68;"mins";"");OR(" hours ";" hour ");",")

Upvotes: 0

Views: 48

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

How about:

 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Q68;"min";"");" hour";",");"s";"")

EDIT#1:

The "best approach":

  1. replace "hour"
  2. replace "min"
  3. replace "s"
  4. replace " "

Upvotes: 2

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Try this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Q68;"s";"");"min";"");" hour ";",")

Upvotes: 3

Related Questions