Tags: column, comma-delimited, create, dynamic, excel, indefinite, microsoft, msdn, numbers, range, software, string, student

Create comma-delimited string from dynamic range?

On Microsoft » Microsoft Excel

3,006 words with 2 Comments; publish: Thu, 22 May 2008 06:29:00 GMT; (30678.13, « »)

I have a column of student ID numbers, a list of indefinite length,

like this:

A

40001

40003

40004

40008

.

.

.

And I just need to create a string from these numbers, comma

delimited, in another cell for use elsewhere. The above would become:

40001,40003,40004,40008.

If the list were not of indefinite length (but not more than, say,

10), it would be very trivial indeed. I can create a dynamic range,

but don't know what to do with it for this application. Any

suggestions for a slick efficient way to get that string via formula?

Or should I just go for a macro to loop through the range?

All Comments

Leave a comment...

  • 2 Comments
    • Hi,

      Here's one way:

      Insert or use a blank column, then if your data starts in A1 in b2 type:

      =A1&","&A2, in B3 type: =B2&","&A3, copy B3 to the last data cell which you

      can then copy and paste, delete the column when done.

      Hope this helps!

      Jean-Guy

      "Ptyrider" wrote:

      > I have a column of student ID numbers, a list of indefinite length,

      > like this:

      > A

      > 40001

      > 40003

      > 40004

      > 40008

      > ..

      > ..

      > ..

      > And I just need to create a string from these numbers, comma

      > delimited, in another cell for use elsewhere. The above would become:

      > 40001,40003,40004,40008.

      > If the list were not of indefinite length (but not more than, say,

      > 10), it would be very trivial indeed. I can create a dynamic range,

      > but don't know what to do with it for this application. Any

      > suggestions for a slick efficient way to get that string via formula?

      > Or should I just go for a macro to loop through the range?

      >

      #1; Thu, 22 May 2008 06:30:00 GMT
    • Thanks, not a bad idea!

      pinmaster wrote:

      > Hi,

      > Here's one way:

      > Insert or use a blank column, then if your data starts in A1 in b2 type:

      > =A1&","&A2, in B3 type: =B2&","&A3, copy B3 to the last data cell which you

      > can then copy and paste, delete the column when done.

      > Hope this helps!

      > Jean-Guy

      > "Ptyrider" wrote:

      > > I have a column of student ID numbers, a list of indefinite length,

      > > like this:

      > >

      > > A

      > > 40001

      > > 40003

      > > 40004

      > > 40008

      > > ..

      > > ..

      > > ..

      > >

      > > And I just need to create a string from these numbers, comma

      > > delimited, in another cell for use elsewhere. The above would become:

      > > 40001,40003,40004,40008.

      > >

      > > If the list were not of indefinite length (but not more than, say,

      > > 10), it would be very trivial indeed. I can create a dynamic range,

      > > but don't know what to do with it for this application. Any

      > > suggestions for a slick efficient way to get that string via formula?

      > > Or should I just go for a macro to loop through the range?

      > >

      #2; Thu, 22 May 2008 06:31:00 GMT