REGEXREPLACE to Wrap Numbers in Brackets (with QUERY Use Case)

With the help of the REGEXREPLACE function, we can easily wrap numbers in a text string in brackets in Google Sheets.

This is useful for formatting strings that contain numbers. But beyond formatting, there’s one practical use of adding brackets around numbers in Google Sheets — especially when working with the QUERY function.

As the title suggests, we can use the above REGEXREPLACE method to dynamically refer to columns when using aggregation functions in QUERY.

Let’s start with how to use REGEXREPLACE to wrap numbers in brackets in Google Sheets. We’ll get to the QUERY part afterward.

Table of Contents

How to Add Brackets Around Numbers Using the REGEXREPLACE Function in Google Sheets

In the example below, I’m using parentheses to wrap numbers. But you can use square brackets instead if preferred.

Syntax:

=REGEXREPLACE(text, "(\d+(?:\.\d+)?)", "($1)")

Example 1:

If the content of cell A1 is:

Purchase 100.50, Damage 50

The following REGEXREPLACE formula in cell B1 will return:

Purchase (100.50), Damage (50)

=REGEXREPLACE(A1, "(\d+(?:\.\d+)?)", "($1)")

Let’s look at another example.

Example 2:

This time, the value in cell A1 is just a number:

Here, the above formula won’t work directly, because the content in A1 is a pure number. The REGEXREPLACE function only works with text.

To make it work, convert the number to text using A1&"":

=REGEXREPLACE(A1&"", "(\d+(?:\.\d+)?)", "($1)")

This way, you can use the REGEXREPLACE function to wrap numbers in brackets in Google Sheets, regardless of whether the input is text or a standalone number.

REGEXREPLACE to Wrap Numbers in Brackets and Its Use in the Query Select Clause

Now that you know how to wrap numbers in brackets using REGEXREPLACE, let’s see how this helps with dynamic column references in the QUERY function.

Query Function – Static (Non-Dynamic)

Let’s say we have the following sample data:

Standard QUERY formula in Google Sheets summing fixed columns using SUM(Col2), SUM(Col3), and SUM(Col4)

To sum columns C, D, and E (i.e., columns 2, 3, and 4 in the range B1:E), the usual QUERY formula would be:

QUERY(B1:E, "SELECT SUM(Col2), SUM(Col3), SUM(Col4)", 1)

Why This Fails on Column Changes

If you insert a new column before column E, the data range B1:E will automatically adjust — but the query string won’t. That’s because the query argument is a hardcoded string and won’t reflect column changes.

Query Function – Dynamic with REGEXREPLACE

Let’s make the query string dynamic using our earlier REGEXREPLACE trick.

Step 1: Generate the column numbers using SEQUENCE:

=SEQUENCE(1, COLUMNS(B1:E1)-1, 2)

This generates:

2	3	4

(The formula returns a single-row array starting from 2, with as many columns as in B1:E1 minus one, which matches the number of columns in C1:E1.)

Step 2: Wrap the numbers in `Sum(ColX)` using REGEXREPLACE

First, convert the number array to text using &"":

=SEQUENCE(1, COLUMNS(B1:E1)-1, 2)&""

Then wrap it with ARRAYFORMULA and apply REGEXREPLACE:

=ARRAYFORMULA(REGEXREPLACE(SEQUENCE(1, COLUMNS(B1:E1)-1, 2)&"", "(\d+(?:\.\d+)?)", "Sum(Col$1)"))

Output:

Sum(Col2)
Sum(Col3)
Sum(Col4)

Now join them using TEXTJOIN:

=TEXTJOIN(",", TRUE, ARRAYFORMULA(REGEXREPLACE(SEQUENCE(1, COLUMNS(B1:E1)-1, 2)&"", "(\d+(?:\.\d+)?)", "Sum(Col$1)")))

Result:

Sum(Col2),Sum(Col3),Sum(Col4)

Step 3: Plug into the QUERY formula

=QUERY(B1:F, "SELECT "&TEXTJOIN(",", TRUE, ARRAYFORMULA(REGEXREPLACE(SEQUENCE(1, COLUMNS(B1:E1)-1, 2)&"", "(\d+(?:\.\d+)?)", "Sum(Col$1)"))), 1)

Optional: Add a Group By clause

=QUERY(B1:F, "SELECT Col1, "&TEXTJOIN(",", TRUE, ARRAYFORMULA(REGEXREPLACE(SEQUENCE(1, COLUMNS(B1:E1)-1, 2)&"", "(\d+(?:\.\d+)?)", "Sum(Col$1)")))&" WHERE Col1 IS NOT NULL GROUP BY Col1", 1)

Wrapping Up

I hope this tutorial helped you understand how to use REGEXREPLACE to wrap numbers in brackets in Google Sheets — not just for formatting, but also as a real-life dynamic solution in the QUERY function.