In column A of sheet 2 (sheet2.A), I’d like to use a formula with two functions:
Is it possible to do this with one single formula, and if so, what would it be?
Start with:
=TRIM($sheet1.$A1)
and pull down, or use:
=TRIM($sheet1.$A$1:$A$1000)
and enter with ctr+shift+enter as ArrayFormula, respective the Array-option in Formula Wizard.
Thank you!
Just so that I understand array formulas: I know that they are protected against changes, but will
=TRIM($sheet1.$A$1:$A$1000)
do the same as the array formula
{=TRIM($sheet1.$A$1:$A$1000)}
No. TRIM() takes a scalar parameter, not a range or matrix, and as such for a non-array formula an implicit intersection with the formula cell position is created, for example if entered in row 1 then =TRIM($sheet1.$A$1:$A$1000) returns the trimmed cell of Sheet1.A1, in row 2 of Sheet1.A2, … and if in row 1001 it returns an error because there is no intersection with the range given.