Count tokens in a string

[Robert Crandal]

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

 

[Claus Busch]

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

 

[Rick Rothstein]

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)