Comma separated string element count

Please Login or Register  to view this content.

Regards, TMS

The previous code counts commas. This counts elements:

Please Login or Register  to view this content.

Regards, TMS

I was going to mention the "+1" TMS.

I chose not to hide zeros here:

Please Login or Register  to view this content.

Or you could use a function such as the following, and then use a formula in column K:

Please Login or Register  to view this content.

Formula in K12 would be:

=UOM_Count(I12)

Fill down as many rows as you want. You can format the cells to hide zeros if you want, too. (Same would apply to the macro version.)

Of course, if you wanted to forego all code and just use native Excel functions, try:

=LEN(I12)-LEN(SUBSTITUTE(I12,",",""))+IF(I12="",0,1)

@Paul: to be honest, the first response was just a case of "fixing" the posted code, and then attempting to replicate it but shorter/quicker. And, what it does is count the commas. Fair enough, made it do that. Then I looked again at the first sentence which says "comma separated elements" ... hence the revisit.

And the only reason for avoiding the zeros is that my test data had more data in column E than it did in column I.

Regards, TMS

You're welcome.

@raogm2001:

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.