## Forum Discussion

# 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

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

- Martin_AngostoIron Contributor
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.

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

- Martin_AngostoIron Contributor
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:

- NikolinoDEGold Contributor
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**- lejoS1225Copper ContributorHi 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)
- NikolinoDEGold ContributorIn Norwegian.

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

- lejoS1225Copper ContributorNikolino, that did work. It gave me decimals 1,13 when the value in A1 is "1 min 8 sec"
- NikolinoDEGold Contributor
Here is an example in the file.

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

- PeterBartholomew1Silver Contributor
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))) ))`