Google Sheets – How to Extract the Nth Occurrence of a Number from a String

google sheets

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

Best Answer

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} ")

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

should work.

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

Related Solutions

Google Sheets – How to Split, Trim, and Sum Numbers in Text Cells

If you have a row of data like A1:F1 you could use this formula in excel

=SUMPRODUCT((0&MID(A1:F1,{2;7;12;17;22},3))+0)

or this in google-spreadsheets

=arrayformula(SUM((0&MID(A1:F1,{2;7;12;17;22},3))+0))

That assumes each number is 3 characters like 3.5 and allows for up to 5 numbers per cell - you can extend the {2;7;12;17;22} part if there might be more than 5 per cell - or for high numbers that part can be automated based on cell length.

If you have a column of data like A1:A10 then the separators in {2;7;12;17;22} need to change to commas so that would be like this in excel

=SUMPRODUCT((0&MID(A1:A10,{2,7,12,17,22},3))+0)

or this in google-spreadsheets

=arrayformula(SUM((0&MID(A1:A10,{2,7,12,17,22},3))+0))

blank cells are allowed in the data so you could make that a larger range for expansion purposes.

Explanation:

If A1 contains this string X5.1 Y4.3 Z2.8 then

=MID(A1,{2,7,12,17,22},3)

will give you this "array"

{"5.1","4.3","2.8","",""}

Notice that the values are included in quotes which means they are text values (MID function like LEFT and RIGHT etc. always returns text values) so we need to convert these text strings to numbers before they can be summed - one way to convert is to do a mathematical operation on that array that won't change the values, e.g. *1 or +0. If we use the latter, though, we get this:

{"5.1","4.3","2.8","",""}+0 = {5.1,4.3,2.8,#VALUE!,#VALUE!}

applying +0 to the non-numeric blank [""] values gives #VALUE! error....which is a problem if we want to sum the results.......so, before adding zero we can concatenate a zero to the front of each result, e.g. using

=0&MID(A1,{2,7,12,17,22},3)

gives the result

={"05.1","04.3","02.8","0","0"}

concatenating a zero to the front of each number won't chage the value of the numeric values but converts the blanks to zeroes, so now when zero is added we get no errors, just:

{5.1,4.3,2.8,0,0}

which can be summed without error.

Extending the range to A1:A10 simply means that the resultant array is 10x5 rather than 1x5 - everything else works the same way.......

Google Sheets – Count Non-Blanks with Unique Values in Another Column

I was able to create two options:

Formula 1

=COUNTUNIQUE(FILTER(A2:A11,E2:E11<>""))

=COUNTA(UNIQUE(FILTER(A2:A11,E2:E11<>"")))

Formula 2

=COUNTA(QUERY(A2:E11, "SELECT COUNT(E) WHERE E<>'' GROUP BY A label COUNT(E) '' "))

Explained

The first two formulas are identical. They filter column A, where column E has entries. Afterwhich column A is filtered for unique names. These names are than counted. The COUNTUNIQUE function combines the COUNTA and UNIQUE.

The second formula will count the number of occurrences per unique business name having an entry (<> '' and group by). The result is counted. Remove the COUNTA function to see the intermediate result.

Example

See you're own example file for the results !!