Concatenate With Delimiter

[golfnut324]

Can someone help me to concatenate across columns, say J through Q, (some of which are blank) and have the results delimited by a comma without returning anything for columns that might be blank. So R660 would read "0263B001A,FX9,FX-9,FX10,FX-10" and R669 would read "08A0477,GPR18,GPR-18". I need to apply this to a about 14000 rows in a worksheet.

Please help me with the details of vba and storing in a personal workbook.

Many thanks to all of your help time and time again.

Craig

Toner Data Base Load 01312009 0623.xls
	J	K	L	M	N	O	P	Q	R
659	0263B001A				FX9	FX-9	FX10	FX-10
660	0263B001A				FX9	FX-9	FX10	FX-10
661	0264B001A
662	0264B001A
663	0264B001A
664	0264B001A
665	0264B001A
666	0264B001A
667	0264B001A
668	0264B001A
669	0384B003AA				GPR18	GPR-18
670	0384B003AA				GPR18	GPR-18
671	08A0477
Sheet1

 

[golfnut324]

here's another custom vba function that might suit your needs. hold alt+f11 and paste the below code into a new vba module (Insert\Module). use like a regular excel function eg = myCate(J659:Q659):

Private Function myCate(Target As Range)
 
Dim c As Range
Dim Temp As String
 
For Each c In Target
 
If c = "" Then
     'do nothing
  Else
     Temp = Temp & c.Value & ","
End If
 
Next
 
myCate = Mid(Temp, 1, Len(Temp) - 1)
 
End Function

Thank you all very much for the great help! I downloaded/installed moreunc,xll and now have all kinds of great tools. For the record, MCONCAT left a comma at the end of the string and on very long strings it removed the comma and one more character.

I installed code from crimson_blade1 (MYCATE) and it worked flawlessly. I'm going to keep working on STRINGCONCAT because I know I must be doing something wrong.

Thanks to you all again!

Craig