Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count letter string, e.g. h/EL/p | Excel Worksheet Functions | |||
Count Occurances of a txt string | Excel Discussion (Misc queries) | |||
Count string IF..... | Excel Worksheet Functions | |||
How to Count String occurences | Excel Programming | |||
Count Spaces In A String | Excel Programming |