0

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'
4

1 Answer 1

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.

1
  • thank you. That solves my issue. Also, thank you for the links, and the $1 too :)
    – Uncl Scott
    Jun 13, 2022 at 23:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.