Function to return a specific number of message recipients

[mleifer001]

I have a a bunch of emails (over 52,000) exported into Excel and I need a function to return emails with less than 5 recipients (including the To, CC, and BCC fields). Also, the email addresses vary based on internal/external addresses so the only way (that I know) to figure out how many recipients there are is to count the commas separating the email addresses. Each email has a corresponding control number, so...

I need a function that would return the control number of emails with less than 5 commas in the To, CC, and BCC fields combined.

Help!!

Thank you

 

[DonkeyOte]

It's not clear how the To, Cc & Bcc data is stored - are these recipient strings stored in separate columns or in one elongated string ?

If we assume you need to calculate based on comma as implied you can subtract the length of the address string without the commas from the same string with commas - the difference (+1 if not blank) is your count of recipients.

Basic example of the above below:

Excel Workbook
	A	B	C	D
1	To	Cc	Bcc	Count
2	Jim D	xyz@somewhere.com,ajskaj@abc.co.uk		3
3	Jim D, zyz@hjds.net		ds@dsjk.com,Dave H,Terry T	5
Sheet5

If you wanted to return the count of mails with < 5 recipients in a single cell calculation and without use of helpers then one approach (extension of above) would be:

=SUMPRODUCT(((($A$2:$A$3<>"")+($B$2:$B$3<>"")+($C$2:$C$3<>""))+(LEN($A$2:$A$3&$B$2:$B$3&$C$2:$C$3)-LEN(SUBSTITUTE($A$2:$A$3&$B$2:$B$3&$C$2:$C$3,",","")))<5)+0)

 

[mleifer001]

Yes, separate To, CC, and BCC fields just as you constructed. I will give this a try but it seems like it will work very well for what I need.

Thank you!!!!