If cell ends with $ then concatenate cell below.

HotLanta

Board Regular
Joined
Nov 3, 2005
Messages
176
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Maybe
VBA Code:
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
 
Upvote 0
I make boo-boo. This is what the error handler should be after the Next line.
VBA Code:
Next

exitHere:
Application.EnableEvents = True
Exit Sub
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
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
AB
1Source ListResult
2xx
3y$y$z&a
4z&ab
5bc$d$ e
6c$f$g
7d$ eh$i
8f$j
9gk
10h$Measure 3
11iOUTPUT,(Point 1),100,100,30$20,25,45
12j
13k
14Measure 3
15OUTPUT,(Point 1),100,100,30$
1620,25,45
Sheet2
Cell Formulas
RangeFormula
B2:B11B2=DROP(TRANSPOSE(SUBSTITUTE(TEXTSPLIT(SUBSTITUTE(TEXTJOIN("",,A2:A16&"|"),"$|","$"),"|"),"|","")),-1)
Dynamic array formulas.
 
Upvote 0
@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
AB
1Source ListResult
2xx
3y$y$z&a
4z&ab
5bc$d$ e
6c$f$g
7d$ eh$i
8f$j
9gk
10h$Measure 3
11iOUTPUT,(Point 1),100,100,30$20,25,45
12j
13k
14Measure 3
15OUTPUT,(Point 1),100,100,30$
1620,25,45
Concat$
Cell Formulas
RangeFormula
B2:B11B2=DROP(TEXTSPLIT(CONCAT(A2:A16&IF(RIGHT(A2:A16,1)="$","","|")),,"|"),-1)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top