Results 1 to 7 of 7

Thread: Concatenating MANY cells

  1. #1

    Concatenating MANY cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,
    I have many cells that contain text (more the 50 columns!), and I need to Concatenate (join) them, ignoring the blank cells, and put " - " between the texts that are concatenated.
    I've tried the Concatenate function but it's not practical (I have to write every single cell in the range).

    Please look at the example in the attached file, and see how I want the result to be in cell J5.
    Again, this is an example; a little part of the many many columns.

    any help.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    To do that you would need a user defined function.

    Here is one I like to use for this:

    Code:
    Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
    For Each y In a.Cells
    aconcat = aconcat & y.Value & sep
    Next y
    ElseIf IsArray(a) Then
    For Each y In a
    aconcat = aconcat & y & sep
    Next y
    Else
    aconcat = aconcat & a & sep
    End If
    
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
    End Function
    You need to add this to the VB editor. Perform an ALT+F11 to open the editor, then go to Insert|Module and paste the code in the editor, then close it.

    Now in J5 of your sheet enter:

    =SUBSTITUTE(TRIM(aconcat(A5:G5," "))," "," - ")

    and copy down.

    note: This assumes there are single words in the cells as per your sample.


  3. #3
    Oh.. almost.. it works on single word but there are cells contain more than one word, like ( john marry) or ( no work ) etc.
    If there are also more than two words, is it the same like 2 words?

    Thank you

  4. #4
    Just use it like so

    =aconcat(A1:B1," - ")

  5. #5
    sry it didn't work! I got this:
    john - mary - - - kate - - brad

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Try with the same UDF I gave you, this formula in J5 of your workbook:

    =SUBSTITUTE(SUBSTITUTE(TRIM(aconcat(SUBSTITUTE(A5:G5," ","^^")," "))," "," - "),"^^"," ")

    this one needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER, before copying down.


  7. #7
    Well done,, thank you so much bro
    Have a good time

Posting Permissions

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