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!

closed as unclear what you're asking by Rubén, ale, serenesat, Alex, Vidar S. Ramdal Mar 20 at 13:01

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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