Merge field with comma after and leave blank

Hi,

I try to merge fields with numbers and field with characters:

field1 field2 field3 field4 field5 field6
1040 5027
1040
1040 5027 1234
1040 1234
1040 1234 1234 1234

I want to merge all fields to one field and have comma after each field (skip blank fields)
like merge field for row3 should be 1040, 5027, 1234. Any one have the same problems?

Thanks in advance for your help.

Try this workbook and UDF

Use the UDF like this

=Conc(A2:G2,", ")

You can use any separator you want with that UDF

Select the range you need to conncatenate, say A2:G2

Try like this
=Conc(A2:G2," | ")

or even
=Conc(A12:G18," kittya ")

No UDF is a User Defined Function.

To use this you need to do this.

1/. In Excel, with your workbook open, press Alt+f11 this will take you to the VBa Editor.

2/. With the "Insert" Tab choose "Module"

3/. In the resulting window paste all of the following code

Please Login or Register  to view this content.

4/. Save the file and close the VBa Editor.

5/. Select the cell you want the result to appear in
Now, in the formula bar type
=Conc(
and without entering the above, select the range you want to concatenate
The formula bar will now look, for example, like this
=Conc(A2:G2
Type a comma then ", ") (including the quotes)
The formula bar should look like this
=Conc(A2:G2,", ")
Press enter

If you leave out the sepatator the function will do a straight concatenation
=Conc(A2:G2)

As described earlier you can change the separator to what you want, look at the demo workbook to see the examples.

=substitute(if(a2="","",a2&",")&if(b2="","",b2&",")&if(c2="","",c2&",")&if(d2="","",d2&",")&if(e2="","",e2&",")&if(f2="","",f2&","),",","",counta(a2:f2))