Count Separated List (1 Viewer)

jaikaoliver

Member
Local time
Today, 05:56
Joined
Nov 18, 2019
Messages
37
Hello team
Is there a way to count my contacts lists separated by commas or semi-colon
eg

07254851,2541555,225555,55555 should return 4 contacts
the contacts are saved in a table field
 

Isaac

Lifelong Learner
Local time
Today, 05:56
Joined
Mar 14, 2017
Messages
8,738
Code:
ubound(split("07254851,2541555,225555,55555",",")) + 1

1607617329891.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:56
Joined
Oct 29, 2018
Messages
21,358
Code:
?Len([ContactList])-Len(Replace([ContactList],",",""))+1
(untested)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:56
Joined
Feb 19, 2002
Messages
42,981
You might want to consider normalizing it so you don't have this problem.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:56
Joined
May 7, 2009
Messages
19,175
make a Robust function.
how many contacts when you have this:

,,,,
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:56
Joined
May 21, 2018
Messages
8,463
If you want to normalize here is an example I just did
 

sxschech

Registered User.
Local time
Today, 05:56
Joined
Mar 2, 2010
Messages
791
Code:
Function StringCountOccurrences(strText As String, strFind As String, _
                                Optional lngCompare As VbCompareMethod) As Long
'http://codevba.com/visual-basic-source-code/vb-string/count_occurrences_in_a_string.htm#.WCd9nYL3ieM
' Counts occurrences of a particular character or characters.
' If lngCompare argument is omitted, procedure performs binary comparison.
'Testcases:
'?StringCountOccurrences("","") = 0
'?StringCountOccurrences("","a") = 0
'?StringCountOccurrences("aaa","a") = 3
'?StringCountOccurrences("aaa","b") = 0
'?StringCountOccurrences("aaa","aa") = 1
'20161112
Dim lngPos As Long
Dim lngTemp As Long
Dim lngCount As Long
    If Len(strText) = 0 Then Exit Function
    If Len(strFind) = 0 Then Exit Function
    lngPos = 1
    Do
        lngPos = InStr(lngPos, strText, strFind, lngCompare)
        lngTemp = lngPos
        If lngPos > 0 Then
            lngCount = lngCount + 1
            lngPos = lngPos + Len(strFind)
        End If
    Loop Until lngPos = 0
    StringCountOccurrences = lngCount
End Function

Example:
? stringcountoccurrences("ABC;CBS;NBC;PBS",";")
3
 

sxschech

Registered User.
Local time
Today, 05:56
Joined
Mar 2, 2010
Messages
791
I misread the original post which I thought was trying to count the number of occurrences of the sep char. In my example, I was counting the number of occurrences for the sep char. I suppose one could modify the function to add 1 to the result? Or maybe this isn't the right function for the request.
 

Isaac

Lifelong Learner
Local time
Today, 05:56
Joined
Mar 14, 2017
Messages
8,738
Yes I think you could just add 1 to your function.
Honestly I was surprised at how long this thread went. My one-liner first posted would have easily solved : )

BUT - as I always preach to others about forum management, the more input on threads the better. So I guess now I have to follow my own rule :p
 

sxschech

Registered User.
Local time
Today, 05:56
Joined
Mar 2, 2010
Messages
791
Too bad didn't know about your great one-liner before. Thanks. Hopefully will remember if I have a need in the future.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:56
Joined
Oct 29, 2018
Messages
21,358
My one-liner first posted would have easily solved : )
Just FYI to all, the OP indicated in Post #5 the potential need to use the solution in a query, which would make the Split() function not qualify, unless it was wrapped in a custom UDF instead. Cheers!
 

Isaac

Lifelong Learner
Local time
Today, 05:56
Joined
Mar 14, 2017
Messages
8,738
Yes, would need to go along w/function.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:56
Joined
Oct 29, 2018
Messages
21,358
Yes, would need to go along w/function.
Not necessarily, if UDF, but definitely an option. The solution used in Post #5 only involved built-in functions.
 

Isaac

Lifelong Learner
Local time
Today, 05:56
Joined
Mar 14, 2017
Messages
8,738
Not necessarily, if UDF, but definitely an option.
Huh?
All i was doing in that post was agreeing with you. Yes, it would need to be in a function, or as you stated, a UDF.
Not understanding you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:56
Joined
Oct 29, 2018
Messages
21,358
Huh?
All i was doing in that post was agreeing with you. Yes, it would need to be in a function, or as you stated, a UDF.
Not understanding you.
Sorry for the confusion. I may have misunderstood what you said. What I was trying to clarify was that creating a UDF (custom function to wrap the Split() function in it) is not necessary, because built-in functions can do the job.
 

Isaac

Lifelong Learner
Local time
Today, 05:56
Joined
Mar 14, 2017
Messages
8,738
They can either use split with a udf, or use your method without a udf. I think we're on the same page.
 

Users who are viewing this thread

Top Bottom