Results 1 to 4 of 4

Thread: Concatinate values in a Range or Array using Native Formula

  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13

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

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

    Thanks in Advance.
    Last edited by LalitPandey87; 02-15-2012 at 02:35 PM.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    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.
    Last edited by Rick Rothstein; 04-02-2012 at 10:20 PM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    Thanks to both of u for your valuable suggestions.

Similar Threads

  1. Create A Dynamic Border Using Excel Native Formula
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-02-2013, 06:07 AM
  2. Use Native Excel Function Lookup Formula In VBA
    By ramananhrm in forum Excel Help
    Replies: 8
    Last Post: 05-03-2013, 09:10 AM
  3. Replies: 16
    Last Post: 04-19-2013, 08:20 PM
  4. counting consecutive values in an array
    By 5ko in forum Excel Help
    Replies: 3
    Last Post: 12-04-2012, 03:49 AM
  5. Combining data of Two Array or Range
    By princ_wns in forum Excel Help
    Replies: 5
    Last Post: 10-01-2012, 06:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •