How can I find the count of semicolon separated values?

Question

I have a list of all email ids which I have copied from the 'To' field, from an email I received in MS Outlook. These values (email ids) are separated by a semicolon. I have copied this big list of email ids into Excel. Now I want to find the number of email ids in this list; basically by counting the number of semi colons.

One way I can do this is by writing C code. i.e. store the big list as string buffer, and keep comparing the chars to ";" in a while(char == ';') loop. But I want to do it quickly.

Is there any quick way to find that out using either:

1.) Regular expression (I use powergrep for processing the regexps)

2.) In excel itself (any excel macro/plugin for that?)

3.) DOS script method

4.) Any other quick way of getting it done?

Answer 1

If counting the number of semicolons is good enough for you, you can do it in Perl using this solution: Perl FAQ 4.24: How can I count the number of occurrences of a substring within a string

Answer 2

I believe the following should work in Excel:

= Len(A1) - Len(Substitute(A1, ";", "")) + 1

/EDIT: if you've pasted the email addresses over several cells, you can count the cells with the following function:

= CountA(A1:BY1)

CountA counts non-empty cells in a given range. You can specify the range by typing =CountA( into a cell and then selecting your cell range with the mouse cursor.

Answer 3

looping over it with a while loop and counting the ';' is probably going to be the fastest, and the most readable.

Consider Konrad's suggestions, that too will loop through the string and check every char and see if it is a simicolon, and then in modifies the string (may or may not be mutable, I don't know with excel), and then it counts the length between it and the original string.

Answer 4

If you are using Excel you can use this code and expose it.

Public Function CountSubString(ByVal RHS As String, ByVal Delimiter As String) As Integer
    Dim V As Variant
    V = Split(RHS, Delimiter)
    CountSubString = UBound(V) + 1
End Function

If you have .NET you can make a little command line utility

Module CountSubString

    Public Sub Main(ByVal Args() As String)

        If Args.Length <> 2 Then
            Console.WriteLine("wrong arguments passed->")
        Else
            Dim I As Integer = 0
            Dim Items() = Split(Args(0), Args(1))
            Console.WriteLine("There are " & CStr(UBound(Items) + 1) & " 
        End If

    End Sub
End Module

Answer 5

Load the list in your favorite (not Notepad!) editor, replace ; by \n, see in the status bar how many lines you have, remove the last line if needed.

Answer 6

Bash/Cygwin One-Liner

$ echo "[email protected];[email protected];[email protected]" | sed -e 's/;/\n/g' | wc -l
3

If you already have Cygwin installed it's effectively instant. If not, cygwin is worth installing IMHO. It basically provides a Linux bash prompt overlaid over your Windows system.

As an aside, stuff like this is why I prefer *nix over Windows for work stuff, I can't live on a windows box without Cygwin since bash scripts are so much more powerful than batch scripts.

Answer 7

C# 3.0 with LINQ would make this easy if it is an option for you over C

myString.ToCharArray().Count(char => char = ';')

Answer 8

PowerShell:

> $a = 'blah;blah;blah'
> $a.Split(';').Count  
3

Answer 9

3) if you don't have neither cygwin, nor powershell installed try this .cmd

@echo off
set /a i = 0
for %%i in ([email protected];[email protected];[email protected]) do set /a i = i + 1
@echo %i%

Answer 10

If awk, echo is awailable (and it is, even on windows):

echo "addr1;addr2;addr3...." | awk -F ";" "{print NF}"