=ARRAYFORMULA(if(isblank(B2:B),"",B2:B))
duplicates the column. But,
=ARRAYFORMULA(if(isblank(B2:B),"",CONCATENATE("B=",B2:B)))
produces a column with values starting with B= , but followed by the juxtaposition of all the row values from the B column in each cell of the result column.
It seems that the effect of B2:B on the left, even though it is two nesting levels down, produces the right result (values only if column B is not blank in each row), but the effect of B2:B on the right, has a different effect, and varies according to conditions I don't understand, sometimes being a single value, and sometimes expanded to a whole array. This makes ARRAYFORMULA have extremely limited usefulness for my application. I was hoping the resulting column would have only a single value from the corresponding row of column B.
Am I missing something about how to use ARRAYFORMULA ?
Is there a different way to achieve my goal of adding B= at the front of each cell in the result column? (Actually, my goal is to do more sophisticated combinations of the cell value and other text, but I reduced it down to this much simpler case which I don't understand, and maybe if I figure this out, I can do the more complex stuff too)
CONCATENATE merges all arrays to a single value. I believe this is what you're looking for:
=ARRAYFORMULA(if(B2:B="","",("B="&B2:B)))
Here's some food for thought. What do you expect the following formula to do?
=ARRAYFORMULA(CONCATENATE(if(isblank(B2:B),"",("B="&B2:B&"😵"))))
Also Try CONCAT, JOIN and TEXTJOIN. Each might look doing the same. But those subtle differences make all the difference in the world.