Hi Tim,

(Im sorry, my computer has some bios/date problem, so Im Re-Posting the
below message after correcting the date/time setting)

Thanks a lot for your code.

I have a small change if possible.

Presently if In A1 I have --- I have measles. I also have TB.
and if in A2 I have --- I want to go to Paris in order to cure my TB.
Then in B1I get the below result

{I} : 2
{have} : 2
{measles} : 1
{also} : 1
{TB} : 1

and for getting the below result in B2 (by running your sub again by
changing the address)

{I} : 1
{want} : 1
{to} : 3
{go} : 1
{Paris} : 1
{in} : 1
{order} : 1
{cure} : 1
{my} : 1
{TB} : 1


I want to know if it is possible to ..

a) To have a consolidated count of words in both the rows. That is the above
result to combine the words in both rows 1 and 2 for giving a total result.
b) Is it is possible to have the count of words listed in different rows
rather than within a single cell (as per help you have used a chr(10) for
linefeed character). Accordingly have the counts in Column C for the
corresponding unique word.

For example for the above example with 2 rows, I visualise the following
result :-

Column -- A
B C
Row 1 I have measles. I also have TB.
I 3
2 I want to go to Paris in order to cure my TB.
Have 2
3
Measles 2
4
also 1
5
TB 2
6
want 1
7
to 3
8
go 1
9
Paris 1
10
in 1
11
order 1
12
cure 1
13
my 1


Actually why am requesting for the above way is I want to analyse count of
unique words across 500 rows or so and a consolidated list would help me a
lot.
-
Regards,
Hari
India

"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Try this. Requires a reference to the Scripting runtime library.

Tim.


Sub tester()
ActiveSheet.Range("B1").Value =
CountWords(ActiveSheet.Range("A1").Value)
End Sub

Function CountWords(sText As String) As String

Dim x As Integer
Dim arrWords As Variant
Dim arrReplace As Variant
Dim oDict As New Scripting.Dictionary
Dim tmp As String
Dim k As Variant
Dim sReturn As String

arrReplace = Array(vbTab, ":", ";", ".", Chr(10), Chr(13))
For x = LBound(arrReplace) To UBound(arrReplace)
sText = Replace(sText, arrReplace(x), " ")
Next x

arrWords = Split(sText, " ")
oDict.CompareMode = TextCompare 'case-insensitive
For x = LBound(arrWords) To UBound(arrWords)
tmp = Trim(arrWords(x))
If tmp < "" Then
oDict(tmp) = IIf(oDict.Exists(tmp), oDict(tmp) + 1, 1)
End If
Next x

sReturn = ""
For Each k In oDict.Keys
sReturn = sReturn & "{" & k & "} : " & oDict(k) & Chr(10)
Next k

CountWords = sReturn
End Function

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










--
Regards,
Hari
India
"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Try this. Requires a reference to the Scripting runtime library.

Tim.


Sub tester()
ActiveSheet.Range("B1").Value =
CountWords(ActiveSheet.Range("A1").Value)
End Sub

Function CountWords(sText As String) As String

Dim x As Integer
Dim arrWords As Variant
Dim arrReplace As Variant
Dim oDict As New Scripting.Dictionary
Dim tmp As String
Dim k As Variant
Dim sReturn As String

arrReplace = Array(vbTab, ":", ";", ".", Chr(10), Chr(13))
For x = LBound(arrReplace) To UBound(arrReplace)
sText = Replace(sText, arrReplace(x), " ")
Next x

arrWords = Split(sText, " ")
oDict.CompareMode = TextCompare 'case-insensitive
For x = LBound(arrWords) To UBound(arrWords)
tmp = Trim(arrWords(x))
If tmp < "" Then
oDict(tmp) = IIf(oDict.Exists(tmp), oDict(tmp) + 1, 1)
End If
Next x

sReturn = ""
For Each k In oDict.Keys
sReturn = sReturn & "{" & k & "} : " & oDict(k) & Chr(10)
Next k

CountWords = sReturn
End Function

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