My strings variables usually contain between 2 to 5
string tokens. Here are some examples:

myVar = "Todd Jones" ' 2 tokens
myVar = "Mary Jo Lynn Jackson" ' 4 tokens
myVar = "Peter James Smith Jr." ' 4 tokens

What is a good way to get the COUNT of the number
of tokens in my string?

Thanks

Hi Robert,

Am Wed, 25 May 2011 13:03:31 -0700 schrieb Robert Crandal:

myVar = "Todd Jones" ' 2 tokens
myVar = "Mary Jo Lynn Jackson" ' 4 tokens
myVar = "Peter James Smith Jr." ' 4 tokens

What is a good way to get the COUNT of the number
of tokens in my string?

try:
=LEN(myVar)-LEN(SUBSTITUTE(myVar," ",))+1


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

My strings variables usually contain between
2 to 5 string tokens. Here are some examples:

myVar = "Todd Jones" ' 2 tokens
myVar = "Mary Jo Lynn Jackson" ' 4 tokens
myVar = "Peter James Smith Jr." ' 4 tokens

What is a good way to get the COUNT of the
number of tokens in my string?

If you know that only one space will always separate each token...

TokenCount = 1 + UBound(Split(myVar))

However, if you cannot be sure of this...

TokenCount = 1 + UBound(Split(WorksheetFunction.Trim(myVar)))

Rick Rothstein (MVP - Excel)

"Rick Rothstein" wrote in message
...

However, if you cannot be sure of this...

TokenCount = 1 + UBound(Split(WorksheetFunction.Trim(myVar)))


Hi Rick. This is a good solution. However, do you know if this code
can be transfered to MS Word VBA as well?? I'm not sure if
Word VBA uses the "WorksheetFunction" reference or not.

thnx

On Wed, 25 May 2011 14:06:23 -0700, "Robert Crandal" wrote:

"Rick Rothstein" wrote in message
...

However, if you cannot be sure of this...

TokenCount = 1 + UBound(Split(WorksheetFunction.Trim(myVar)))


Hi Rick. This is a good solution. However, do you know if this code
can be transfered to MS Word VBA as well?? I'm not sure if
Word VBA uses the "WorksheetFunction" reference or not.

thnx


Word does not support the WorksheetFunction. However, you could set a reference to Excel (Tools/References Microsoft Excel 12.0 Object Library)

However, if you cannot be sure of this...

TokenCount = 1 + UBound(Split(WorksheetFunction.Trim(myVar)))

Hi Rick. This is a good solution. However, do you know if this code
can be transfered to MS Word VBA as well?? I'm not sure if
Word VBA uses the "WorksheetFunction" reference or not.

I don't program in Word, so I don't know for sure, but I would doubt it. Ron
seems more sure of this, so I will yield to his response. However, if you
don't want to set a reference to Excel, I would expect the following code to
work just fine in Word for the case where you are not sure of the number of
spaces between words...

Do While InStr(myVar, " ")
myVar = Replace(myVar, " ", " ")
Loop
TokenCount = 1 + UBound(Split(Trim(myVar)))

You should copy/paste the above rather than retype it so you don't miss the
double spaces between the first sets of quotes in the first two statements
(the second set of quotes contain only a single space).

Rick Rothstein (MVP - Excel)