Excel Tip - Concatenating Ranges of Cells

Every once in a while I get an Excel file that I need to pull data from to use as an in list for a query. I've blogged before about a cool technique that I use to do this (the quick and dirty method). Well, someone just recently requested my help in creating a quick in list out of several hundred elements for use in running a Business Objects report (On a side note - I hate Business Objects). I would have normally used my method, but the only problem with that method is that you have to explicitly tell Excel which cells to concatenate like so:

Also, there is a limit as to how many cells you can concatenate by default in Excel (30).

So, feeling especially lazy today I decided to Google a solution and found this pretty cool answer. It's a custom Excel function that allows you to specify a range of cells to concatenate. Just paste this macro into your project (instructions on where to stick it are here) and call it like so (the second arguments is optional, but will insert a delimiter between each item):

That's it for me for the week. I'm off to the wife's Christmas party.

Comments (0) | Print | Send | Code | del.icio.us | Linking Blogs | 3223 Views