2 functions in 1 formula

In column A of sheet 2 (sheet2.A), I’d like to use a formula with two functions:

  1. reference column A of sheet 1 (=$sheet1.A:$sheet1.A)
  2. apply the TRIM function to its cells

Is it possible to do this with one single formula, and if so, what would it be?

Start with:


and pull down, or use:


and enter with ctr+shift+enter as ArrayFormula, respective the Array-option in Formula Wizard.

1 Like

Thank you!

Just so that I understand array formulas: I know that they are protected against changes, but will


do the same as the array formula


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.

1 Like