Count Occurrences Of Text/Character Within a Cell

[rtgleck]

Hi,

I'm trying to count the number of occurnaces of a character within a cell to use as criteria within an If statement in a Macro.... For example

Hi/This is/ Randy
Hi/this is Randy

I'd like to count the number of "/" within the cell 2 for the first, 1 for the second.

[Andy Pope]

Try this

=LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) 

[rtgleck]

Hmm.. Unless I'm missing something I don't appear to be able to use Substitue within a Visual Basic Macro to use this? Otherwise I think it would work. I.e...

If LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) >1 Then 
    Exit For 
End If 

Hope this makes sense.

[Krishnakumar]

Hi,

May be..

If Len([a1]) - Len(Replace([a1].Value, "/", "")) > 1 Then 
    Exit For 
End If 

[Dave Hawley]

PLEASE use the code tags for any code as you agreed. I think you should read the rules you have agreed to, don't you?

Sub CountChar() 
    MsgBox Len(Range("A1")) - Len(Replace(Range("A1"), "/", "")) 
End Sub 

[Andy Pope]

Sorry rtgleck, did read the in Macro part of the question.

LEN(range("A1"))-LEN(application.WorksheetFunction.SUBSTITUTE(range("A1"),"/","")) 

[rtgleck]

Cool, that worked, thank you

[rtgleck]

My apologies Dave, I guess i didn't completly understand what was being asked for there. I'll make sure I do in the future. Thank you

[Aaron Blood]

Hmm.. Unless I'm missing something I don't appear to be able to use Substitue within a Visual Basic Macro to use this? Otherwise I think it would work. I.e...

If LEN(A1)-LEN(SUBSTITUTE(A1,"/","")) >1 Then 
    Exit For 
End If 

Hope this makes sense.

Well... actually you can use it in VBA the same way you'd use it in a worksheet cell. That is, if you wanted to...

Sub test() 
    x = [Len(A1) - Len(Substitute(A1, "/", ""))] 
    If x > 1 Then 
        MsgBox x 
    End If 
End Sub