# Extract nth occurrence of a number from a string

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

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

• Yes! Thank you, that's what I was looking for! 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. 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". Oct 30, 2017 at 11:04
• 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! Oct 30, 2017 at 12:21

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.