Hi Hari,

You could use a worksheet formula:

=SUMPRODUCT((ref<"")*(LEN(TRIM(ref))+1-LEN(SUBSTITUTE(ref," ",""))))

Regards
Peter T

"Hari" wrote in message
...
Hi,

Suppose in cell A1 I have a text --- "I had some bread in morning. I had
some eggs at night."

Is it possible to programmatically get a count of unique words in the
above
string.
Like in column B the unique words are listed one word in each row in the
following manner..B1 -- "I"
B2 -- "I
B2 -- "had
B3 -- "some"
B4 -- "bread"
B5 -- "in"
B6 -- "morning"
B7 -- "eggs"
B8 -- "at"
B9 -- "night"

Actually I can use excel's text to columns feature ( using space as a
delimiter) but automating is a problem. Like I have 500 or so rows of data
and out of those rows I want to get count of unique words within those 500
rows ( basically a consolidated unique list..). Now, each row would be
having different number of words so, programmatically how would it be
implemented.

Please guide me if possible.

Regards,
Hari
India