SOLVED

# Help with convertions/formula

Copper Contributor

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

Thanks

15 Replies

# Re: Help with convertions/formula

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.

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

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)

# Re: Help with convertions/formula

In Norwegian.
=VERDI(VENSTRE(A1; FINN(" min"; A1) - 1)) + VERDI(DELTEKST(A1; FINN(" min"; A1) + 5; FINN(" sec"; A1) - FINN(" min"; A1) - 5)) / 60

# Re: Help with convertions/formula

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:

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

Thank you so much :)

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

Nikolino, that did work. It gave me decimals 1,13 when the value in A1 is "1 min 8 sec"

# Re: Help with convertions/formula

Here is an example in the file.

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

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

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?

# Re: Help with convertions/formula

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

# Re: Help with convertions/formula

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