count in a string

[SHIRA]

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?

[Tom Ogilvy]

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?

[Ken Valenti]

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?

[SHIRA]

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?

[Tom Ogilvy]

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?

[RB Smissaert]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?