Excel Formula to Count How many Forward Slashes (/) in Cell Value

Question

I need a formula to tell me how many forward slashes (/) I have in a cell.

The cell contains a blue hyperlink path to a folder.

I have tried a Len-Len(Substitute) formula, but it is not working because it returns 0. My expected result is > 0.

The cell with the hyperlink is B6. The cell with my reference character (/) is C6.

My attempted formula is in cell D6.

=LEN(B6)-LEN(SUBSTITUTE(B6,C6,""))

With:

• B6 = Z:\01 Project folders by PAG number range\@PAG Files 0001 to 0035\0001 Wadden Village_Wandle Park Village, Croydon\

• C6 = '/

• D6 = =LEN(B6)-LEN(SUBSTITUTE(B6,"/",""))

D6 returns 0

There are clearly several forward slashes in the path. I need a solution that will return the right value.

Answer 1

replace:

C6 = '/

with:

C6 = '\

Answer 2

As @Gary's Student posted before, I think his solution is the best way for you to achieve what you need in this instance. I am going to include the following, as it is a solution to your title question and hopefully this may help someone in the future.

Dim count as Integer
For i = 1 to Len(B6)
    If Mid(B6,i,1) = "/" Then
        count = count + 1
    End If
Next i
return count

What we are doing is iterating through the cell value B6 in character increments of 1 from the first character to the last to count the number of forward slashes.