Extracting Multiple Occurrences of Text Given Strings in Google Sheets

Method 1: Using the SEARCH Function

The SEARCH function in Google Sheets can be used to find the position of a text string within a cell. By using this function multiple times, we can extract all occurrences of a text string.

Suppose we have the following data in cell A1:

meal cost: $20
metro cost: $5, metro cost: $5

To extract all occurrences of the text "cost:" and the following number, we can use the following formula:

=ARRAYFORMULA(REGEXEXTRACT(A1, "cost: (\$[-\d.]+)"))

This formula uses the REGEXEXTRACT function to extract the text that matches the regular expression pattern "cost: (\$[-\d.]+])". The ARRAYFORMULA function is used to apply this formula to the entire array of results returned by the SEARCH function.

Method 2: Using the REGEXEXTRACT Function

The REGEXEXTRACT function can also be used to extract specific text based on a regular expression pattern. This function can extract multiple occurrences of a text string by using the global flag.

Suppose we have the following data in cell A1:

meal cost: $20
metro cost: $5, metro cost: $5

To extract all occurrences of the text "cost:" and the following number, we can use the following formula:

=REGEXEXTRACT(A1, "cost: (\$[-\d.]+)", "g")

The "g" flag in the third argument of the REGEXEXTRACT function stands for "global", which means that all occurrences of the pattern in the cell will be extracted.