Joining cell content with comma spacing

[Raziel7]

Hi Guys,

Really looking forward to your help with this one.

I needs to combine cell data and have a comma spacing between them. however I don't want empty cells to merge.

For example, [TABLE="width: 500"]
<tbody>[TR]
[TD]1.[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.[/TD]
[TD]Tomato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.[/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5.[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7. [/TD]
[TD]Tomato[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need it to read:

Apple,Tomato,Banana,Apple,Apple,Tomato ( notice how the gap was ignore for row 6 and 8.

I know that the concatenate function can help but I have having trouble with the gap.

I am using the following:

=concatenate(A1,",",A2,","A3) and so on. but the blank cell are affecting the end result.

Thanks in advance for your help,

Raziel

 

[Rick Rothstein]

This will be So much easier if OP (or I) have the TEXTJOIN function.

I don't have the version of Excel with TEXTJOIN either, but depending on the workbook is to be used, having it may not be the answer. If the workbooks is to be used by more than the OP, those individuals would also have to have the version of Excel with TEXTJOIN in it as well or the formulas would fail when they opened the workbook.

 

[Raziel7]

I don't have the version of Excel with TEXTJOIN either, but depending on the workbook is to be used, having it may not be the answer. If the workbooks is to be used by more than the OP, those individuals would also have to have the version of Excel with TEXTJOIN in it as well or the formulas would fail when they opened the workbook.

Thanks Rick,

I thinking Concatenate will do the trick, but I need to try to remove the ,,,,,,,,,,, at the end.

I have simply put the function in another cell where I can copy the combined cells and paste on to another program.

regards,

Raziel

 

[jtakw]

My updated formula in Post #10 should do it, try it with your data.

 

[Raziel7]

Hi PVR928, Jtakw and Rick Rothstein,

Thank you all very much for your help. I can't thank you enough.

I used the following, and it worked perfectly:

=SUBSTITUTE(TRIM(CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",A11," ",A12))," ",",")

I am still interested in the VBA option and will give it a go with the UDF suggestion.

you guys are champions and thanks again,

Raziel

 

[jtakw]

Hi PVR928, Jtakw and Rick Rothstein,

Thank you all very much for your help. I can't thank you enough.

I used the following, and it worked perfectly:

=SUBSTITUTE(TRIM(CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",A11," ",A12))," ",",")

I am still interested in the VBA option and will give it a go with the UDF suggestion.

you guys are champions and thanks again,

Raziel

That's my formula from Post #8 , but as Rick pointed out, if you have 2 or more words in the same cell, it'll fail, my formula in Post #10 should work just as well, but will not fail with more than 1 word in a single cell. If there won't be more than 1 word in a single cell, than the Post #8 formula should work fine.

 

[Raziel7]

Hi jtakw,

Your formula in post #8 is perfect.

Thanks again.

Raziel

 

[jtakw]

You're welcome, welcome to the forum.

 

[pvr928]

Hi Raziel7

This is not as dynamic as my first attempt, and it assumes all of the text is contained only in the range A9:A40 on worksheet "LOAD SHEET".

The output is in range C1 of worksheet "LOAD SHEET".

Option Explicit
Sub ConcatenateStrings()
Dim ws As Worksheet
Dim rRange As Range
Dim rCell As Range
Dim sOutputString As String
Set ws = Sheets("LOAD SHEET")
Set rRange = ws.Range("A9:A40")
For Each rCell In rRange
    
    If rCell.Row = 9 Then
        
        sOutputString = Trim(rCell.Value2)
            
    Else
        
        sOutputString = sOutputString & "," & Trim(rCell.Value2)
            
    End If
        
Next rCell
ws.Range("C1") = sOutputString
End Sub

Cheers

pvr928