You could get the text from the cell and use split() to create an
array of words. Exactly how you did this would depend on what you'd
consider as a "word".

You might want to use replace() on the string before splitting it to
replace newlines, tabs etc with spaces

'eg (untested)
dim sTxt as string, arrWords
sTxt=activesheet.range("A1").value
sTxt=replace(sTxt,vbTab," ")
sTxt=replace(sTxt,vbcrlf," ")
arrWords=split(sTxt," ")

Once you have the array you could loop through it and count the
occurences of the words: one way might be to use a dictionary object
where the word is the key and the value would be incremented each time
the word crops up. Then loop though the dictionary and write the
contents to column B.

tim






"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