Count occurances of specific character in string

[Guest]

Is there an easy way to count the occurances of a specific character in a
string?

For instance, if a table contains a list of account and subaccount codes
delimited with periods such as: "SALES.41400.27" I would like a formula that
counts the number of periods in the total string, (2 in this case).

TIA

TK

 

[Brendan Reynolds]

Here's what I posted in reply to the same question in
microsoft.public.access two days ago ...

Nothing built-in, no, but you can do it with a few lines of VBA ...

Public Function CountInString(ByVal strCountIn As String, ByVal strCountThis
As String) As Long

Dim lngLoop As Long
Dim lngCount As Long

For lngLoop = 1 To Len(strCountIn)
If Mid$(strCountIn, lngLoop, Len(strCountThis)) = strCountThis Then
lngCount = lngCount + 1
End If
Next lngLoop

CountInString = lngCount

End Function

? countinstring ("this is some text","is")
2

 

[Guest]

Thanks Brendan

I should have expanded my search a bit.

I guess a custom function is the way to go, but I was also able to get the
correct results by combining the Len() and replace functions as follows:

Len([Account])-Len(Replace([Account],".",""))

This works, but as I said, I like your function better.

Cheers!

TK

 

[Brendan Reynolds]

Hold on there, TK! I like your solution better than mine - it's probably
faster.
--
Brendan Reynolds (MVP)

Thanks Brendan

I should have expanded my search a bit.

I guess a custom function is the way to go, but I was also able to get the
correct results by combining the Len() and replace functions as follows:

Len([Account])-Len(Replace([Account],".",""))

This works, but as I said, I like your function better.

Cheers!

TK

Here's what I posted in reply to the same question in
microsoft.public.access two days ago ...

Nothing built-in, no, but you can do it with a few lines of VBA ...

Public Function CountInString(ByVal strCountIn As String, ByVal
strCountThis
As String) As Long

Dim lngLoop As Long
Dim lngCount As Long

For lngLoop = 1 To Len(strCountIn)
If Mid$(strCountIn, lngLoop, Len(strCountThis)) = strCountThis
Then
lngCount = lngCount + 1
End If
Next lngLoop

CountInString = lngCount

End Function

? countinstring ("this is some text","is")
2

 

[Marshall Barton]

I guess a custom function is the way to go, but I was also able to get the
correct results by combining the Len() and replace functions as follows:

Len([Account])-Len(Replace([Account],".",""))

How clever!

I guess it takes a fresh view of things to get me out of a
rut. After all these years, I never thought of that simple
expression.

Now I just have to find a spare brain cell to remember it
;-)

 

[Guest]

Well, you know what they say about the blind squirrel...

Thanks guys.

Thanks Brendan

I should have expanded my search a bit.

I guess a custom function is the way to go, but I was also able to get the
correct results by combining the Len() and replace functions as follows:

Len([Account])-Len(Replace([Account],".",""))

This works, but as I said, I like your function better.

Cheers!

TK

Here's what I posted in reply to the same question in
microsoft.public.access two days ago ...

Nothing built-in, no, but you can do it with a few lines of VBA ...

Public Function CountInString(ByVal strCountIn As String, ByVal strCountThis
As String) As Long

Dim lngLoop As Long
Dim lngCount As Long

For lngLoop = 1 To Len(strCountIn)
If Mid$(strCountIn, lngLoop, Len(strCountThis)) = strCountThis Then
lngCount = lngCount + 1
End If
Next lngLoop

CountInString = lngCount

End Function

? countinstring ("this is some text","is")
2

 

[Brendan Reynolds]

Check out the thread "HOW DO I FIND NUMBER OF OCCURENCES FOR DIFFERENT
LETTERS IN A FIEL" in the newsgroup microsoft.public.access.tablesdbdesign -
you are now famous!

--
Brendan Reynolds (MVP)

Well, you know what they say about the blind squirrel...

Thanks guys.

Thanks Brendan

I should have expanded my search a bit.

I guess a custom function is the way to go, but I was also able to get
the
correct results by combining the Len() and replace functions as follows:

Len([Account])-Len(Replace([Account],".",""))

This works, but as I said, I like your function better.

Cheers!

TK

Here's what I posted in reply to the same question in
microsoft.public.access two days ago ...

Nothing built-in, no, but you can do it with a few lines of VBA ...

Public Function CountInString(ByVal strCountIn As String, ByVal
strCountThis
As String) As Long

Dim lngLoop As Long
Dim lngCount As Long

For lngLoop = 1 To Len(strCountIn)
If Mid$(strCountIn, lngLoop, Len(strCountThis)) = strCountThis
Then
lngCount = lngCount + 1
End If
Next lngLoop

CountInString = lngCount

End Function

? countinstring ("this is some text","is")
2

--
Brendan Reynolds (MVP)


Is there an easy way to count the occurances of a specific character
in a
string?

For instance, if a table contains a list of account and subaccount
codes
delimited with periods such as: "SALES.41400.27" I would like a
formula
that
counts the number of periods in the total string, (2 in this case).

TIA

TK