0

Sample Spreadsheet

I have an =IMPORTHTML function in my spreadsheet that imports a table on a website containing information I need for my spreadsheet. One of these imported cells contains a text string with three different numbers in it. I only need the third number. How can I extract this third number?

I've tried using the following formula, but it only gives me the first occurrence of a number:

=regexextract(A1,"[0-9]+")
1

String in A2:

Represents a 90% confidence range based on a sample of 280 runs.
100 runs are done at a time.
Add data to the log (requires JavaScript).

=REGEXEXTRACT(A2,"(\d+) runs ")

If the test string is of the exact format as shown above, the above REGEX should work.

  • \d+ represents one or more digits.
  • () inside REGEX represent capture group.

EDIT:

Based on the clarification that test strings can also be "run" instead of "runs",

=REGEXEXTRACT(A2,"(\d+) run(?:s){0,1} ")

Or

=REGEXEXTRACT(A2,"(?m)^(\d+)")

should work.

Ref: https://github.com/google/re2/blob/master/doc/syntax.txt

  • 1
    Yes! Thank you, that's what I was looking for! – Gnrl Kitty Oct 30 '17 at 10:53
  • Actually, for some reason, it doesn't work if the number is more than one digit long. It takes the right-most number. So "10" becomes "0" for example. – Gnrl Kitty Oct 30 '17 at 10:57
  • The first formula would work perfectly except that the string changes if the third number is "1". Instead of saying "runs" it says "run". – Gnrl Kitty Oct 30 '17 at 11:04
  • 2
    Completely perfect. I'm sorry about the late clarification. It wasn't until I tested your first function that I discovered "runs" changes to "run" if the number was "1". Thank you again! – Gnrl Kitty Oct 30 '17 at 12:21
2

Since the text string is always identical with the exception of the three numbers in it, it appears I've managed to do what I want with this:

=RIGHT(index(split(A2, " "),12), LEN(index(split(A2, " "),12))-6)

It's not as clean as what I was hoping for, but it seems to work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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