If cell ends with $ then concatenate cell below.

[HotLanta]

Hi,

I have csv files. The data is always in column A. I would like to have a macro that runs down the rows and if a cell ends with "$" then concatenate the cell below. The data is in cell A1 down to about A500.

So if cell A21 ends with "$" then concatenate the contents of cell A22, then keep on going.

Would someone be able to help out with this?

Thank you

 

[KRice]

Yours and mine both assume that the final cell does not end with "$"

Good point about the last cell...I suppose the easiest way to address that possibility, for either formula, is to extend the range reference to include the cell below that last entry:

=DROP(TEXTSPLIT(CONCAT(A2:A17&IF(RIGHT(A2:A17,1)="$","","|")),,"|"),-1)

 

[Peter_SSs]

I suppose the easiest way to address that possibility, for either formula, is to extend the range reference to include the cell below that last entry:

I don't like that option since it would include an empty cell in the spill range if the last cell does not end with $. This may affect subsequent formulas/results.
I also don't think worth worrying about at this stage since the OP asked for a vba solution & has one.

 

[KRice]

Agreed, then it becomes messier...could add a conditional to check RIGHT(last cell,"$") and DROP either -1 or -2 from the final array, or perform the check in the initial step. But also not worth revising if there is no need.