Concatinate values in a Range or Array using Native Formula

[LalitPandey87]

Hi All

Hope you are good.

After a long time i am here again looking for your help.

Is it Possible to concatenate a range or array by using Native formula.

i.e. :- =CONCATENATE({"A";"B";"C";"D"}) will give A but i need ABCD.

I can do it with the help of VBA but looking for a formula which will give the desired output.

Thanks in Advance.

[Rick Rothstein]

Unfornuately, CONCATENATE does not work with ranges or arrays. For what you posted, the obvious formula answer would be...

=CONCATENATE("A","B","C","D")

but I think your ultimate question is deeper than that. I am afraid a VBA solution is the best you will be able to do. I know you said you can do your own VBA solution, but thought you would not mind seeing one that I have posted in the past.

This function...

Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
    Dim Cell As Range, Area As Variant
    For Each Area In CellRanges
        If TypeName(Area) = "Range" Then
            For Each Cell In Area
                If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value
            Next
        Else
            ConCat = ConCat & Delimiter & Area
        End If
    Next
    ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function

allows you to create formulas like this...

=ConCat("-",A1:A3,C1,"HELLO",E1:E2)

and the contents of the indicated cells, and the word "HELLO", will be concantenated together, in the order shown, with a dash between them. The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a placeholder in order to delineate the argument position). So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...

=ConCat("",A1:A3,C1,"HELLO",E1:E2)

or like this (note the leading comma)...

=ConCat(,A1:A3,C1,"HELLO",E1:E2)

your choice.

[Admin]

Hi

You could achieve this with the help of a helper column. Have a look at this Concatenate Multiple Lookup (Unique) Values into a Single Cell

[LalitPandey87]

Thanks to both of u for your valuable suggestions.