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]

What version of Excel are you using?

 

[Micron]

Maybe

Sub Concat()
Dim Lrow As Long, i As Integer
Dim rng As Range

On Error GoTo errHandler
Application.EnableEvents = False
Lrow = Cells(rows.count, "A").End(xlUp).Row

For i = 1 To Lrow
    Set rng = Range("A" & i)
    If Right(rng.Offset(1), 1) = "$" Then
        rng = rng & rng.Offset(1)
        rng.Offset(1) = ""
    End If
Next

Application.EnableEvents = True
exitHere:
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[Micron]

I make boo-boo. This is what the error handler should be after the Next line.

Next

exitHere:
Application.EnableEvents = True
Exit Sub

 

[HotLanta]

Hi, this is taking the contents of the cell with the $ and concatenating it with the cell above.
I was wanting to concatenate the cell below to the end of the cell with the $.
So

Measure 3
OUTPUT,(Point 1),100,100,30$
20,25,45

should end up as

Measure 3
OUTPUT,(Point 1),100,100,30$20,25,45

I really do appreciate your help. This is closer than I had gotten on my own.

 

[HotLanta]

I got it. Thank you very much Micron for your help.

 

[Micron]

Glad I could help. I took this

if a cell ends with "$" then concatenate the cell below.

to mean concatenate the cell below into the cell that ends in $. That's kinda what concatenation means - to append something to what you already have.

 

[KRice]

I never heard back from you about your Excel version, so I have no idea if a solution that uses functions available in Excel 365 would be relevant. Nevertheless, here is another option that requires Excel 365 functions and assumes you can use some character that does not appear in your source list as a delimiter. For example, I've chosen a pipe character "|" to use as a delimiter.

MrExcel_20240224.xlsx
	A	B
1	Source List	Result
2	x	x
3	y$	y$z&a
4	z&a	b
5	b	c$d$ e
6	c$	f$g
7	d$ e	h$i
8	f$	j
9	g	k
10	h$	Measure 3
11	i	OUTPUT,(Point 1),100,100,30$20,25,45
12	j
13	k
14	Measure 3
15	OUTPUT,(Point 1),100,100,30$
16	20,25,45
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=DROP(TRANSPOSE(SUBSTITUTE(TEXTSPLIT(SUBSTITUTE(TEXTJOIN("",,A2:A16&"|"),"$|","$"),"|"),"|","")),-1)
Dynamic array formulas.

 

[Peter_SSs]

@HotLanta
Even though you already have a resolution here, please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

here is another option that requires Excel 365 functions

I think this does the same job. Yours and mine both assume that the final cell does not end with "$"

24 02 26.xlsm
	A	B
1	Source List	Result
2	x	x
3	y$	y$z&a
4	z&a	b
5	b	c$d$ e
6	c$	f$g
7	d$ e	h$i
8	f$	j
9	g	k
10	h$	Measure 3
11	i	OUTPUT,(Point 1),100,100,30$20,25,45
12	j
13	k
14	Measure 3
15	OUTPUT,(Point 1),100,100,30$
16	20,25,45
Concat$

Cell Formulas
Range	Formula
B2:B11	B2	=DROP(TEXTSPLIT(CONCAT(A2:A16&IF(RIGHT(A2:A16,1)="$","","|")),,"|"),-1)
Dynamic array formulas.

 

[KRice]

Better yet...thanks, Peter.