Concatenating MANY cells

saltarazan

New member
Joined
Jan 18, 2013
Messages
16
Reaction score
0
Points
0
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.
 

Attachments

  • conc1.xlsx
    8.6 KB · Views: 41

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

saltarazan

New member
Joined
Jan 18, 2013
Messages
16
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Just use it like so

=aconcat(A1:B1," - ")
 

saltarazan

New member
Joined
Jan 18, 2013
Messages
16
Reaction score
0
Points
0
sry it didn't work! I got this:
john - mary - - - kate - - brad
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 
Top