Haw to count ";" in a string
 

Hi experts
Haw do i count number of ; in my string z
im using this :

z = "bla;bla;bla;bla"
x = Len(z) - Len(Application.WorksheetFunction.Substitute(z, ";", ""))

is there a VBA command for this or is my way the only way ?

tks. in advance

Haw to count ";" in a string
 

z = "bla;bla;bla;bla"
MsgBox Len(z) - Len(Replace(z, ";", ""))

If this post helps click Yes
---------------
Jacob Skaria


"excelent" wrote:

Hi experts
Haw do i count number of ; in my string z
im using this :

z = "bla;bla;bla;bla"
x = Len(z) - Len(Application.WorksheetFunction.Substitute(z, ";", ""))

is there a VBA command for this or is my way the only way ?

tks. in advance

Haw to count ";" in a string
 

oh ok but thats fine too

tanks alot Jacob

"Jacob Skaria" skrev:

z = "bla;bla;bla;bla"
MsgBox Len(z) - Len(Replace(z, ";", ""))

If this post helps click Yes
---------------
Jacob Skaria


"excelent" wrote:

Hi experts
Haw do i count number of ; in my string z
im using this :

z = "bla;bla;bla;bla"
x = Len(z) - Len(Application.WorksheetFunction.Substitute(z, ";", ""))

is there a VBA command for this or is my way the only way ?

tks. in advance

Haw to count ";" in a string
 

On Tue, 27 Oct 2009 10:48:01 -0700, excelent
wrote:

Hi experts
Haw do i count number of ; in my string z
im using this :

z = "bla;bla;bla;bla"
x = Len(z) - Len(Application.WorksheetFunction.Substitute(z, ";", ""))

is there a VBA command for this or is my way the only way ?

tks. in advance

I don't know if this is any "better", but it is shorter:

UBound(Split(z, ";"))
--ron

Haw to count ";" in a string
 

Haw do i count number of ; in my string z
im using this :

z = "bla;bla;bla;bla"
x = Len(z) - Len(Application.WorksheetFunction.Substitute(z, ";", ""))

is there a VBA command for this or is my way the only way ?

tks. in advance

I don't know if this is any "better", but it is shorter:

UBound(Split(z, ";"))

The Len method is more efficient than the Split method, but that would only
factor in if this code was being executed in a very large loop where the
time differences would accumulate. I tend to use this Split method for these
kinds of things as long as I am not involved in a large loop.

--
Rick (MVP - Excel)