Extracting Specific numbers from text on Google Sheets?

user_13654989 0
Viewed 49

The data is as in the form below. I am trying to extract the hour(h), and minute(m) from the text to calculate the total duration in google sheets. ex 3h 24m equals 3.4 I am not able to extract the minute part incorrectly.

I have tried the below functions which work well for the hour part but fail for the minute part. 

=LEFT(c1, SEARCH("h",c1)-1)

=IFERROR((REGEXEXTRACT(D1,"([[:print:]]+)h")),0)

C        D
4h       2h 49m
32m 19s  2h 2m
6h 7m    3h 24m
google-sheets filter regex
1 Answers

To extract hours, minutes and seconds into separate columns, you may use three separate REGEXEXTRACT formulas:

=IFERROR(REGEXEXTRACT(C1,"(\d+)h"), "")
=IFERROR(REGEXEXTRACT(C1,"(\d+)m"), "")
=IFERROR(REGEXEXTRACT(C1,"(\d+)s"), "")

Here, (\d+) is a capturing group that grabs 1 or more digits and the letter after is just matched, and thus, is not returned by the function.

You may also try to extract each part using optional patterns:

=REGEXEXTRACT(C1, "(?:(\d+)h)?\s*(?:(\d+)m)?\s*(?:(\d+)s)?")

Here, all hour, minute and second patterns are optional, and minutes will still match even if hours are missing.

Details

  • (?:(\d+)h)? - an optional hour part wrapped with a non-capturing group:
    • (\d+) - Group 1: one or more digits
    • h - h letter
  • \s* - 0+ whitespaces
  • (?:(\d+)m)? - an optional minute part:
    • (\d+) - Group 2: one or more digits
    • m - m letter
  • (?:(\d+)s)? - an optional seconds part:
    • (\d+) - Group 3: one or more digits
    • s - s letter
user_38329700
Related