Excel 2007 : Combining Numerous Data From Many Cells Into One Cell

Hi,

I have some customer number data, whereas each customer number is in it's own cell. I want to combine all the customer numbers into one cell, with each number seperated by a comma.

I know I can use the =A1&","&A2 thing, but there's 25+ numbers to combine.

All numbers are 6 digits long.

Is there an easier way to do this, say with the Concantenate Function?

Sample spreadsheet attached.

Thanks
Rick

Concatenate will be for all intents and purposes, just as tedious to enter initially.

Excel doesn't have a concatenate range function but if a UDF is allowable you could try this

Please Login or Register  to view this content.

Enter as this, to accept the default delimiter (", ")

Please Login or Register  to view this content.

or e.g.

Please Login or Register  to view this content.

to use a different delimiter

Hope this helps

To use the Function

1/. With Excel open Press Alt+f11 this will open the VBa Editor.
2/. In the VBa editor Insert > Module
3/. Paste all of the above code in the resultant pane/window.
4/. Save and close the VBa Editor.

Then follow the instructions in Post #2

How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The function is installed and ready to use as shown above originally.

Happy to have been of some help.

Good Morning,
I've been using this solution for about a year now, and everything's been great! Tried this morning, and my code appears to be intact, but excel is acting like it doesn't recognize anything.

So, I opened a new module, inserted the code again, and nothing. I'm getting a "#NAME? error, and also an "ambiguous name" message.

Any ideas?

Thanks
Rick

Originally Posted by Marcol

Concatenate will be for all intents and purposes, just as tedious to enter initially.

Excel doesn't have a concatenate range function but if a UDF is allowable you could try this

Please Login or Register  to view this content.

Enter as this, to accept the default delimiter (", ")

Please Login or Register  to view this content.

or e.g.

Please Login or Register  to view this content.

to use a different delimiter

Hope this helps

1/. Did you enable macros when you opened the workbook? It can be easy to hit the wrong button when you are in a hurry.

2/. If you added a new module and re-entered the code you will get an "ambiguous name" message, unless you deleted the old code.
This is because you have two function with the same name, it doesn't matter that they are in different modules.

Excel can get itself in a tangle from time to time, usually closing Excel then reopening is enough to cure it.
If you had to reboot then probably the OS didn't load Excel properly, this can happen when your hard disc is running at over 70%-80% used.