SOLVED

Help with convertions/formula

Copper Contributor

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 am willing to do it in two steps if needed. Does someone have a solution for this.

 

Thanks

15 Replies

@lejoS1225 

 

Hi, you can try something like this:

 

=TEXT(TIME(0,TEXTBEFORE(A1," m"),MID(A1,FIND("n ",A1)+1,3)),"h:mm:ss")

 

Change reference A1 accordingly.

@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

@Martin_Angosto Thank you for the reply. Get erreor message "to many arguments for this function"

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

 

Hi, is your initial cell format exactly as "19 min 3 sec", including these spaces and characters?

 

With my formula I can go from the cell in the top to the one in the bottom:

 

Martin_Angosto_0-1716458533800.png

Martin_Angosto_1-1716458561750.png

 

 

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?
best response confirmed by lejoS1225 (Copper Contributor)
Solution

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

Thank you so much :)
Hi Martin. For some reason i only get "Value" as end result. This is the Norwegian version that i use: =TEKST(TID(0;TEKST(F2;"m");DELTEKST(F2;FINN("n";F2)+1;3));"T:MM:SS")
Nikolino, that did work. It gave me decimals 1,13 when the value in A1 is "1 min 8 sec"

@lejoS1225 

Here is an example in the file.

The formula has been adapted so that it also works from Excel 2013.

That is just great. Thank you so much for your help. Much appreciated :)

@lejoS1225 

 

Hi! Please note the blank spaces in my formulation. I believe you are writing "m" and "n", whereas in my formulation I also include blank spaces there. Have you tried to copy and paste it only changing the names to norwegian?

@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)))
  ))
1 best response

Accepted Solutions
best response confirmed by lejoS1225 (Copper Contributor)
Solution

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

View solution in original post