Using arrayformula to add suffix, but would like to skip empty cells

Question

I am using an arrayformula to add .png suffix to the text in column A. Right now it looks like this:

=arrayformula(A:A &".png" )

Since I want this to be a part of a macro, I won't manually be able to choose the exact range. So how do I limit the formula, to only add the suffix, if the cells in column A had any text in it, to begin with? Right now I end up with a lot of cells where it just says ".png" because the cell was empty.

I have tried playing around with =if(istext(A:A) but I couldn't figure out how to construct the statement. And maybe it is not the way to go?

player0 player0 · Accepted Answer · 2019-08-12T10:24:51

try:

=ARRAYFORMULA(INDIRECT("A1:A"&COUNTA(A:A))&".png")

or shorter:

=ARRAYFORMULA(IF(A:A="",,A:A&".png")

or regex:

=ARRAYFORMULA(REGEXREPLACE(A:A, "(.+)", "$1.png"))

Using arrayformula to add suffix, but would like to skip empty cells

3 Answers