On Wed, 3 Jan 2007 06:42:00 -0800, Rajat wrote:

Dear Ron Rosenfeld

thanks for the help and i'm extremely sorry for the late reply
The following formula provided by you worked well
=COUNTDIFF(REGEX.MID(A1,"[^,]+",INTVECTOR(REGEX.COUNT(A1,"[^,]+"),1)))

but i'm having problem when there is only 1 text the formula show the text
not the number i.e.

When Cell A Contain - Formula Result
Roy,Roy,b,c - 3
Roy,,, - Roy

i need to count the unique text entry in the cell, can you please solve it,

Regards

Rajat

That is an interesting issue. I will discuss it with Longre. The issue seems
to be that the single item is not being returned as an array-constant, so
COUNTDIFF apparently returns the item, and not the count.

However, a work around, which forces the single item to be returned as an
array, would be to use the ARRAY.JOIN function:

=COUNTDIFF(ARRAY.JOIN(REGEX.MID(A1,"[^,]+",
INTVECTOR(REGEX.COUNT(A1,"[^,]+"),1))))

This seems to work as well as the previous.


--ron