Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Possible Duplicate:
Excel - columns to one cell

I have a list of product skus in one column in excel. I have thousands of these skus that need to be combined in one cell separated by commas with no spaces. There are too many rows of data to use the concatenate function. Not sure how to get this done. Here's an example of what I'm working with but with 6,000+ more rows. I'm using Excel 2003.

A
140-12
1074-156
903-78
876-65
349-09
986-43
237-12
342-11
450-187
677-133
share|improve this question

marked as duplicate by studiohack Sep 18 '12 at 3:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1  
Pretty sure there's a limit to the number of characters per cell in Excel. I can safely say what you are trying to do will exceed that limit. You can try to export it to a CSV file and edit that with Notepad++ to achieve your desired result but I doubt excel will import that into one cell. –  Zooks64 Sep 2 '12 at 17:21
    
Yes, according to this even Excel 2010 has a 32,767 character limit on the total number of characters that a cell can contain. –  HeatfanJohn Sep 2 '12 at 17:36
    
You may have luck with some of the answers in this question. superuser.com/q/404148/143655 –  dangowans Sep 17 '12 at 22:02

3 Answers 3

Bearing in mind the other answers about the cell character limit, do you want to just output a text file with the format as required? If you do, I would export to CSV, then use a text editor: Vim on Windows will be quickest, but probably the least intuitive.

Open the file in Vim and then type :%s/\n/,/g and hit enter. All newlines will be replaced with commas. File > Save and you're done.

share|improve this answer

Beware, even if you do this with a VB macro, according to this even Excel 2010 has a 32,767 character limit on the total number of characters that a cell can contain.

With 6000 rows this would only allow an average of 5 characters per SKU.

share|improve this answer

If you are comfortable with creating your own functions in VB code, there is a StringConcat function shown here that may meet your needs.

Concatenating text in a range of cells:

=StringConcat("|",B1:B5)

returns the values from B1:B5, each separated by a | character.

Of course, you'll still need to watch the character limitation of 32,767 which I think you're going to hit.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.