Forum Discussion

lejoS1225's avatar
lejoS1225
Copper Contributor
May 23, 2024

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

NikolinoDE's avatar
NikolinoDE
Gold Contributor

lejoS1225 

Here is a suggested solution

=VALUE(LEFT(A1, FIND(" min", A1) - 1)) + VALUE(MID(A1, FIND(" min", A1) + 5, FIND(" sec", A1) - FIND(" min", A1) - 5)) / 60

lejoS1225's avatar
lejoS1225
Copper Contributor
May 23, 2024
Hi and thank you for the reply. For some reson this did not work on my end. I just get "#Name?" in the result cell. I have changed it to my F2 cell and repaced , with ; (Norwegian)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 23, 2024
    In Norwegian.
    =VERDI(VENSTRE(A1; FINN(" min"; A1) - 1)) + VERDI(DELTEKST(A1; FINN(" min"; A1) + 5; FINN(" sec"; A1) - FINN(" min"; A1) - 5)) / 60
    • lejoS1225's avatar
      lejoS1225
      Copper Contributor
      May 23, 2024
      What a service 🙂 Yes this did translate "1 min 8 sec" to 1,13 and "1 min 13" sec to 1,26. Why not 1,8 and 1,13?
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        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).

Resources