count in a string
 

Hi,
I have a string that lools like this "F000025"
I would like to build a macro that count how many zero i have in my string
(4 for this example)
or maybe there is a vba function that does that.

Anyone?

count in a string
 

s = "F000025"
cnt = len(s) - len(Application.Substitute(s,"0",""))

in xl2000 or later you can use replace

s = "F000025"
cnt = len(s) - len(replace(s,"0",""))


--
Regards,
Tom Ogilvy

"SHIRA" wrote:

Hi,
I have a string that lools like this "F000025"
I would like to build a macro that count how many zero i have in my string
(4 for this example)
or maybe there is a vba function that does that.

Anyone?

count in a string
 

Try this

Sub CountInString()
Dim COuntIt
Const TheStringIn = "F000025"
MsgBox Application.CountA(TheStringIn, "0")
End Sub


"SHIRA" wrote:

Hi,
I have a string that lools like this "F000025"
I would like to build a macro that count how many zero i have in my string
(4 for this example)
or maybe there is a vba function that does that.

Anyone?

count in a string
 

thanks!

"Tom Ogilvy" wrote:

s = "F000025"
cnt = len(s) - len(Application.Substitute(s,"0",""))

in xl2000 or later you can use replace

s = "F000025"
cnt = len(s) - len(replace(s,"0",""))


--
Regards,
Tom Ogilvy

"SHIRA" wrote:

Hi,
I have a string that lools like this "F000025"
I would like to build a macro that count how many zero i have in my string
(4 for this example)
or maybe there is a vba function that does that.

Anyone?

count in a string
 

from the immediate window you can see this returns 2:
TheStringIN = "F000025"
? Application.CountA(TheStringIn, "0")
2


It will return 1 for each non blank argument. You have supplied 2 non-blank
arguments, so it returns 2.

so similarly:
TheStringIN = "F99999"
? Application.CountA(TheStringIn, "0")
2

--
Regards,
Tom Ogilvy



"Ken Valenti" wrote:

Try this

Sub CountInString()
Dim COuntIt
Const TheStringIn = "F000025"
MsgBox Application.CountA(TheStringIn, "0")
End Sub


"SHIRA" wrote:

Hi,
I have a string that lools like this "F000025"
I would like to build a macro that count how many zero i have in my string
(4 for this example)
or maybe there is a vba function that does that.

Anyone?

count in a string
 

If speed is an issue you may want to use a function like this:

Function CountString2(strChar As String, _
strString As String) As Long

Dim i As Long
Dim n As Long
Dim btArray() As Byte
Dim btAscChar As Byte

If InStr(1, strString, strChar, vbBinaryCompare) = 0 Or _
Len(strString) = 0 Then
CountString2 = 0
Exit Function
End If

btAscChar = Asc(strChar)
btArray = strString

For i = 0 To UBound(btArray) - 1 Step 2
If btArray(i) = btAscChar Then
n = n + 1
End If
Next

CountString2 = n

End Function


RBS


"SHIRA" wrote in message
...
Hi,
I have a string that lools like this "F000025"
I would like to build a macro that count how many zero i have in my string
(4 for this example)
or maybe there is a vba function that does that.

Anyone?