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
  • The unprotected workbook is at TinyUrl.com/CovidHerdTracker. Please note, it takes a while to update, and if you want to experiment, feel free to test on it, using 'This Sheet' in the CTRL+H options. Thank you
    – Uncl Scott
    Commented Jun 13, 2022 at 18:51
  • Welcome to Web Applications Stack Exchange. Commented Jun 13, 2022 at 20:08
  • The sample spreadsheet is too large to be practical for testing. See How to create a Minimal, Reproducible Example. Commented Jun 13, 2022 at 20:13
  • @doubleunary, Thank you for the welcome. And yeah, I'm not exactly sure how the array formulas got into sheets.. which might be my second question ( in another post ).
    – Uncl Scott
    Commented Jun 13, 2022 at 22:59

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
    Commented Jun 13, 2022 at 23:19

Your Answer

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

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