One Example Of Regular Expressions Being Used In Google Sheets
Hi there. This is a quick post here. A colleague showed me one way of using a regular expression in Google Sheets. We wanted to find a way to extract one or more digits from text in a cell. Then we were able to use the extracted numbers and obtain total counts through summation.
Google Sheets' REGEXEXTRACT Function
The main function in Google Sheets that allows for regular expressions is the REGEXEXTRACT() function.
Its first argument is a given text with the second argument as the regular expression in quotes. In the Google documentation , it mentions that regular_expression - The first part of text that matches this expression will be returned.
In the work scenario, the numbers can be extracted with the regular expression \d+ for extracting 1 or more numbers in a given text (cell).
Quick Example
In a short picture example (screenshot) below, I have a list of fruits with their counts in text. (This example is unrelated to my work scenario.) I want to extract to numbers in each cell and then have a total count of the number of fruits.
Using =SUM(A2:A5) would give an error as we have numbers and text. There is no way to add up text.
The =REGEXTRACT(A2, "\d+") formula extracts the 7 from the 7 Strawberries text in cell A2. Continuing this for the other cells gives 5, 12 and 10.
For some reason, doing =SUM(B2:B5) gives 0. To obtain the total count, I had to do long way of addition. That is doing B2 + B3 + B4 + B5 to get the 34.00 (I did number formatting with two decimal places.)
