Menu
I'm trying to concatenate a bunch of columns in Excel. I know I can manually do with:
=A1&", "&B1&", "&C1 (and so on)
but I have about 40 columns, and I'm looking for a way to streamline this process.
Thanks in advance for any help!
510
For this, select all the columns (select first column, press and hold shift key, click on the last column) and press right click and then select “Merge”. After that, from Merge window, select space as a separator and name the column. In the end, click OK and click on “Close and Load”.
CONCATENATE Excel Range (Without any Separator)Select the entire formula and press F9 (this converts the formula into values). Remove the curly brackets from both ends. Add =CONCATENATE( to the beginning of the text and end it with a round bracket). Press Enter.
As a user function taking a range
Public Function ClarkeyCat(ByRef rng As Range) As Variant
Dim c As Range
Dim ans As Variant
For Each c In rng
If (c.Value <> "") Then
ans = IIf(ans = "", "", ans & ",") & c.Value
End If
Next
ClarkeyCat = ans
End Function
Changing the Variant types, if you need to (to string, most likely).
Use like this:
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
