Forum Discussion

lejoS1225's avatar
lejoS1225
Copper Contributor
May 23, 2024
Solved

Help with convertions/formula

Hi. I have a data source that gives me data formatted as: "19 min 3 sec". The cell is "standard". Is it possible to somehow convert this to only numbers, or minutes and seconds without the text? I a...
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    May 23, 2024

    In the former formula is the time is in decimal.
    here a formula in hh:mm:sec
    =TID(0; VENSTRE(A1; FINN(" min"; A1) - 1); DELTEKST(A1; FINN(" min"; A1) + 5; FINN(" sec"; A1) - FINN(" min"; A1) - 5))

    You just need to format the cell(s) accordingly (select cell or range of cells > right mouse button > format cells... > custom > type: hh:mm:ss).

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 23, 2024

lejoS1225 

Some further methods.  The first substitutes the text

= TIMEVALUE(
    SUBSTITUTE(
      SUBSTITUTE(timeText, "min", ":"),
   "sec", "")
  )

whilst the second extracts digits and is only available in 365 insider beta channel

 

= MAP(timeText, LAMBDA(txt,
    TIMEVALUE(TEXTJOIN(":",,REGEXEXTRACT(txt, "(\d+)", 1)))
  ))

Resources