Counting the number of backslashes in a string

[John Klaassen]

Hi you all,

Can anybody tell me if it is possible to count the number of
backslashes in a string? I need the number to appear in a cell.
So if in B1 is "a\b\c" the formula should return 2 in C1.
Any help is appreciated!

Regards,
John

 

[Glen Mettler]

One way (in a macro)

Function SlashCount(MyStr As String)
Dim Slash As Integer
Dim RetVal
Slash = 0
For s = 1 To 256
'If InStr(s, myval, "/") > 0 Then
If Mid(MyStr, s, 1) = "/" Then
Slash = Slash + 1
End If
Next
SlashCount = Slash
End Function

Create this in a module and then in C1 enter =SlashCount(B1)

Glen

 

[Harlan Grove]

John Klaassen wrote...

Can anybody tell me if it is possible to count the number of
backslashes in a string? I need the number to appear in a cell.
So if in B1 is "a\b\c" the formula should return 2 in C1.

Don't use VBA for something this simple.

C1:
=LEN(B1)-LEN(SUBSTITUTE(B1,"\",""))

More generally, the number of instances of substring ss found in string
s is given by

=(LEN(s)-LEN(SUBSTITUTE(s,ss,"")))/LEN(ss)