How to convert a column into a comma-delimited list in MS Excel

---

Comma delimited data lists are often used as input for platforms such as SAP Business Objects, Java, Visual basics, etc. Here is the procedure for creating a comma delimited list:

STEP 1] Concatenate the first two data entries in the list by typing:  =A2&”,”&A3
Here is a depiction were I have applied this for a list of Order IDs

STEP 2] Go to the cell below the cell in step 1 and enter the following formula there:
=C2&","&A4

Here is a depiction. What this formula does is that it concatenates the third column entry with the already concatenate two values of the column.

STEP 3] Drag down the formula in step 2 so it covers the entire range. Here is how it will look like after you apply the formula to the full range.

As you could observe in the image above the delimited data has been generated correctly. While this approach is very effective, it does have one short-coming:  each cell in MS Excel could contain no more than 1024 characters. In other words the maximum length of a cell in MS Excel is equal to 1024.

This shortcoming has affected our comma delimited list as follows:

As you could see in the screenshot above, the comma delimited formula has stopped adding “Order ID” values to the list at a certain point (i.e. at row 128) and this is because the cell length would exceed 1024 after this point. Here is the solution to this anomaly:  
 
First] Insert this formula next to the first data entry of the column:         =Len (C2)
And then apply the formula to the entire range. This formula counts the number of characters in the comma delimited cells.

Second] whenever the length exceeds 1024, reset the formula in column C to the one in STEP 1.

The number of times you will need to reset your comma delimited formula will depend on the number of data entries you have. For example, In this case I will have two comma-delimited lists which I will merge together in MS word.

---