Concatenate and ignore blanks

**nujwaan** · 09-22-2009, 03:29 AM

Hi I'd like to concatenate the two columns in the file I've attached as a string of text that I can copy to another sheet.

The second column in the file attached will be a vlookup from another sheet.

However as shown in the file, I'd like to ignore blank cells and only concatenate if they have data in them.

Please help! I'm sure it's simple but have been stuck with this!

Nujwaan.

**DonkeyOte** · 09-22-2009, 04:13 AM

If you have small sets of data as per your example perhaps:

Please Login or Register  to view this content.

Called from a cell along lines of:

Please Login or Register  to view this content.

**nujwaan** · 09-22-2009, 04:18 AM

Hi DonkeyOte thanks for that.

Where would I enter this code? Do i need to assign it to a button or something? and what do you mean by the second bit of code you gave me?

I'm a newbie to VBA so apologies!

Nujwaan.

**DonkeyOte** · 09-22-2009, 04:22 AM

The VBA code (UDF) should be placed into a Module in VBE.

UDF's can be invoked from a cell as per other excel functions (VLOOKUP, SUMIF etc...), so in whichever cell you want the list to appear simply add the appropriate formula:

=NAMEGRADE(A3:A16,B3:B16,", ")

where A3:A16 represents your range of Names, B3:B16 the range containing the associated Grades and finally ", " (ie comma followed by space) which represents the delimiter to be used ... ie how you intend to split the various names (grades) from one another in the final list.