Welcome to another exciting Google Docs Sheets tutorial! In this guide, I’ll show you how to extract numbers within square brackets in each row in Google Sheets. Whether you have sentences or phrases in a column, with or without multiple square brackets, I’ve got you covered.
Extracting Numbers Within a Single Square Bracket
To extract numbers within a single square bracket in a cell, you can use the REGEXEXTRACT formula. Let’s take a look at an example:
Value in cell B1: Team A [200]
=iferror(REGEXEXTRACT(B1, "[(.+)]"))
Result: 200
Array Formula to Extract Numbers Within Square Brackets
If you have multiple values in a range, such as B1:B, you can convert the above formula into an array formula. This formula will work for multiple values:
=ArrayFormula(iferror(REGEXEXTRACT(B1:B, "[(.+)]")))
Extracting Numbers Within Multiple Square Brackets in Each Row
But what if you have multiple square brackets in the same cell? Don’t worry, I’ve got a solution for you!
Let’s take a look at the sample data:
To extract numbers within multiple square brackets in each row, follow these steps:
Step 1: Filter Out Blank Rows at the End of the Source Data Range
Make sure to filter out any blank rows at the end of the source data range. Use the following formula:
=filter(B2:B,B2:B<>"")
Step 2: Split the Text Strings Using “[” as the Delimiter
Split the text strings using the opening square bracket “[” as the delimiter. Here’s the formula:
=ArrayFormula(split(filter(B2:B,B2:B<>""),"["))
Step 3: Extract the Numbers with Closing Square Brackets
Next, we’ll use the same REGEXEXTRACT formula as before, but with a slight modification. Since we used the opening square bracket as the delimiter in the split, we need to adjust the regular expression. Use the following formula:
=ArrayFormula(iferror(REGEXEXTRACT(D2:F5, "(.+)]")))
Final Formula: Multiply the Output to Make Numbers Numeric
To make the extracted numbers numeric and replace blank cells with zeros, multiply the output by 1. Use the following formula:
=ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B<>""),"["), "(.+)]"))*1)
How to Sum the Extracted Numbers in Each Row in Google Sheets
Here’s an additional tip for you! By using the MMULT function, you can easily sum the extracted values in each row.
Take the last formula we used (the output formula) as Matrix 1 in the MMULT function. Use the following syntax:
MMULT(matrix1, matrix2)
To create Matrix 2, use the following formula:
={1;1;1}
However, if you want to make the MMULT formula dynamic, use the following formula for Matrix 2:
=ArrayFormula(row(indirect("A1:A"&columns(ArrayFormula(iferror(REGEXEXTRACT(split(filter(B2:B,B2:B<>""),"["), "(.+)]"))*1))))^0)
That’s all there is to it! With these formulas, you’ll be able to extract and sum numbers within square brackets in Google Sheets effortlessly.
Remember, if you have any questions or need further assistance, you can always visit Crawlan.com for more Google Sheets tips and tutorials. Happy extracting!
