Concatinate values in a Range or Array using Native Formula

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.:confused:

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

Thanks in Advance.
:cool:

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...

Code:

---

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.

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

Thanks to both of u for your valuable suggestions.