How to count the number of delimiters in a string - VBA

I need to be able to calculate the number of periods in a string variable using Excel VBA.

What's a good way to do so?

Example:

1.1 = 1
1.1.1 = 2
1.1.1.1 = 3

Thanks
JMO9966Asked:
Who is Participating?
 
bromy2004Connect With a Mentor Commented:
Dim Str() as string
dim Delim as integer

str=split(YourValue)
Delim=Ubound(str)
0
 
bromy2004Commented:
Woops
Sub test()
Dim Str() As String
Dim Delim As Integer
Const YourValue As String = "1.1.1.1"


Str = Split(YourValue, ".")
Delim = UBound(Str)

End Sub

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
You can also do it by comparing lengths:

Debug.Print (Len(OriginalStr) - Len(Replace(OriginalStr, Delimiter, ""))) / Len(Delimiter)

If the Delimiter is a single character, that of course reduces to:

Debug.Print Len(OriginalStr) - Len(Replace(OriginalStr, Delimiter, ""))
0
 
JMO9966Author Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.