Remove all Array_Constrain(ArrayForumla(*),1,1) formulas when importing from Excel to Google Sheets using RegEx

Question

My first question here. I have an Excel Spreadsheet, where I have not used CSE, which I import into Google Sheets. Upon importing, some 40k+ cells have =Array_Constrain(ArrayFormula(*),1,1) in them. Except for two cells, in 1 sheet, across 50+ sheets, I do not need the formulas.

I spent over an hour, going sheet by sheet, to remove all "ArrayFormula". This left me with and extra set of () in the formulas, and I'm not looking forward to spending a few more hours removing all the Array_Constrains.

I'm wondering if someone can give me a CTRL+H and the RegEx expression to

1. convert '=Array_Constrain((other_functions)),1,1)' to just '=other_functions' and for next time
2. convert '=Array_Constrain(ArrayFormula(other_functions),1,1) to just '=other_functions'

Answer 1

To get started, choose Edit > Find and replace (Control+Shift+H) and use something like this:

• Find: =ARRAY_CONSTRAIN\(\s*ARRAYFORMULA\(\s*(.+)\s*\), 1, 1\)
• Replace with: =$1
• Search: All sheets
• 🔲  Match case
• ☑️  Search using regular expressions
• ☑️  Also search within formulas
• Replace all

Then do the same with =ARRAY_CONSTRAIN\(\s*\(\s*(.+)\s*\)\s*\), 1, 1\) and =ARRAY_CONSTRAIN\(\s*(.+)\s*\), 1, 1\).

To learn the exact regular expression syntax used by Google Sheets, see RE2.