Haw to count ";" in a string

[excelent]

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[excelent]

oh ok but thats fine too

tanks alot Jacob

"Jacob Skaria" skrev:

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

Haw do i count number of ; in my string z

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.