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

 

[pvr928]

Hi Raziel7

I'm sure someone can come up with a formula answer, but this is what I've put together in VBA.

It assumes all of your strings are in column A and the first string appears in row 1.

Option Explicit
Sub ConcatenateStrings()
Dim ws As Worksheet
Dim lLastRow As Long
Dim rRange As Range
Dim rCell As Range
Dim sOutputString As String
Set ws = Sheet1
lLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set rRange = Range(ws.Cells(1, 1), ws.Cells(lLastRow, 1))
For Each rCell In rRange
    If rCell.Value2 <> "" Then
    
        If rCell.Row = 1 Then
        
            sOutputString = Trim(rCell.Value2)
            
        Else
        
            sOutputString = sOutputString & "," & Trim(rCell.Value2)
            
        End If
        
    Else
    
    End If
    
Next rCell
Debug.Print sOutputString
End Sub

Cheers

pvr928

 

[jtakw]

Hi,

If you must use the CONCATENATE function, this will help "fix" the gaps; if you have Excel 2016 (which I don't have), I believe the CONCAT and/or TEXTJOIN function may do a much better job at it:

Excel 2010
	A	B	C
1	Apple		Apple,Tomato,Banana,Apple,Apple,Tomato,Pear
2	Tomato
3	Banana
4	Apple
5	Apple
6
7	Tomato
8
9	Pear
Sheet29

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(CONCATENATE(A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,",",A9),",,",",")

 

[Raziel7]

HI PVR928,

Thank you for your efforts.

Just a few questions:

1. What is the VBA meant to do? does it give what I need to a specific cell?
2. I am getting an error: - Compile error, invalid inside procedure
3. to be more specific, I only need within the column A9 - A40. anything within that range I need in one cell with a comma separation and ignoring blanks.
4. Not sure if this helps but the work sheet I am working from is titles " LOAD SHEET "

once again I can't thank you enough for you time and effort.

 

[Raziel7]

Hi Jtakw,

Thank you for your help,

I nearly had it working but it is still showing ,,,,,,,,,,,, at the end.

If you don't mind, your help again would be appreciated.

To be more specific, the rows are from A9 to A40.

I tried your code within A9 to A40. I only had content in the first 4 rows and left everything else blank.

I ended up with the following result based on the code you kindly posted:

TOS-00011656,TOS-00011657,TOS-00011658,TOS-00011659,,,,,,,,,,,,,,,


This is what I wrote based on your suggestion:

=SUBSTITUTE(CONCATENATE(A9,",",A10,",",A11,",",A12,",",A13,",",A14,",",A15,",",A16,",",A17,",",A18,",",A19,",",A20,",",A21,",",A22,",",A23,",",A
24,",",A25,",",A26,",",A27,",",A28,",",A29,",",A30,",",A31,",",A32,",",A33,",",A34,",",A35,",",A36,",",A37,",",A38,",",A39,",",A40,","),",,",",")

I need it to end without the ,,,,,,,,,,,,,,,

Thanks again,

Raziel

 

[Rick Rothstein]

Here is a UDF (user defined function) that should work (assuming your values are in Column A)...

[table="width: 500"]
[tr]
	[td]Function ConCat() As String
  ConCat = Replace(Replace(Replace(Application.Trim(Replace(Replace(Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), Chr(1)), " ", Chr(2)), Chr(1), " ")), " ", Chr(1)), Chr(2), " "), Chr(1), ", ")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ConCat just like it was a built-in Excel function. For example,

=ConCat()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Finalfight40]

Hi Raziel7

Can you use:

=TEXTJOIN(",", TRUE, A1:A7)

Where A1:A7 is the range of the data you are trying to join?

 

[jtakw]

Hi Jtakw,

Thank you for your help,

I nearly had it working but it is still showing ,,,,,,,,,,,, at the end.

If you don't mind, your help again would be appreciated.

To be more specific, the rows are from A9 to A40.

I tried your code within A9 to A40. I only had content in the first 4 rows and left everything else blank.

I ended up with the following result based on the code you kindly posted:

TOS-00011656,TOS-00011657,TOS-00011658,TOS-00011659,,,,,,,,,,,,,,,


This is what I wrote based on your suggestion:

=SUBSTITUTE(CONCATENATE(A9,",",A10,",",A11,",",A12,",",A13,",",A14,",",A15,",",A16,",",A17,",",A18,",",A19,",",A20,",",A21,",",A22,",",A23,",",A
24,",",A25,",",A26,",",A27,",",A28,",",A29,",",A30,",",A31,",",A32,",",A33,",",A34,",",A35,",",A36,",",A37,",",A38,",",A39,",",A40,","),",,",",")

I need it to end without the ,,,,,,,,,,,,,,,

Thanks again,

Raziel

Try this formula:

Excel 2010
	A	B	C
1	Apple		Apple,Tomato,Banana,Apple,Apple,Tomato,Pear
2	Tomato
3	Banana
4	Apple
5	Apple
6
7	Tomato
8
9	Pear
10
11
12
Sheet29

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

 

[Rick Rothstein]

Try this formula:

Excel 2010
	A	B	C
1	Apple		Apple,Tomato,Banana,Apple,Apple,Tomato,Pear
2	Tomato
3	Banana
4	Apple
5	Apple
6
7	Tomato
8
9	Pear
10
11
12
Sheet29

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

What if the text in a single cell somewhere contains two or more words (e.g., Bosc Pear)?

 

[jtakw]

What if the text in a single cell somewhere contains two or more words (e.g., Bosc Pear)?

Excel 2010
	A	B	C
1	Apple		Apple,Tomato,Banana,Apple,Apple,Bosc Pear,Tomato,Pear
2	Tomato
3	Banana
4	Apple
5	Apple
6	Bosc Pear
7	Tomato
8
9	Pear
10
11
12
13
14
15
Sheet29

Cell Formulas
Range	Formula
C1	=SUBSTITUTE(SUBSTITUTE(CONCATENATE(A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,",",A9,",",A10,",",A11,",",A12,",",A13,",",A14,",",A15),",,",","),",,,","")

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