# Extract and graph information from a days-hours-minutes-seconds format [closed]

I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? I'm not an expert on Google Sheets so any assistance is appreciated!

• what exactly you want to plot in chart/graph? and what chart/graph you want to go with? – user0 Mar 12 at 19:53

User0 did an amazing job of breaking down how to convert your race times into different durations that can be used to create a graph.

You will need to use one of those formulas (i would suggest the last one which is in seconds), so cut this:

=INT(TO_TEXT(QUERY(ARRAYFORMULA(VALUE(REGEXEXTRACT(SPLIT(A1, " "), "\d+"))), "select (Col1*24)+Col2 label (Col1*24)+Col2''")&":"& JOIN(":", QUERY(ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, " "), "\d+")), "select Col3,Col4")))*86400)

and add it to the column beside your list of race times. It is important with an array formula not to press enter, you MUST press Ctrl + Shift + Enter (Cmd + Shift + Enter on a Mac) and Google Sheets will add the ArrayFormula. Then copy the formula down so there is one beside every race time, again press Ctrl + Shift + Enter.

If that worked okay you should see the amount of seconds beside each racetime, if not please start again from the beginning.

If you have a suitable list of converted race times, you can create a graph and use the column with seconds as the data range.

• to extract numbers from the string:

``=ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, " "), "\d+"))``

• to create duration:

``````=QUERY(ARRAYFORMULA(VALUE(REGEXEXTRACT(SPLIT(A1, " "), "\d+"))),
"select (Col1*24)+Col2 label (Col1*24)+Col2''")&":"&
JOIN(":", QUERY(ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, " "), "\d+")),
"select Col3,Col4"))``````

• to create duration value:

``````=JOIN(":", QUERY(ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, " "), "\d+")),
"select Col2,Col3,Col4"))+REGEXEXTRACT(A1, "\d+")``````

• to convert on seconds/minutes/hours:

``````=INT(TO_TEXT(QUERY(ARRAYFORMULA(VALUE(REGEXEXTRACT(SPLIT(A1, " "), "\d+"))),
"select (Col1*24)+Col2 label (Col1*24)+Col2''")&":"&
JOIN(":", QUERY(ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1, " "), "\d+")),
"select Col3,Col4")))*86400)``````

• for minutes: `*1440`
• for hours: `*24`