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]+")

2 Answers 2

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

4
  • 1
    Yes! Thank you, that's what I was looking for!
    – Gnrl Kitty
    Commented Oct 30, 2017 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
    Commented Oct 30, 2017 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
    Commented Oct 30, 2017 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
    Commented Oct 30, 2017 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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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