0

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!

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

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.

0
  • to extract numbers from the string:

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

    0

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

    0

  • to create duration value:

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

    0

  • 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

    0

Not the answer you're looking for? Browse other questions tagged or ask your own question.