• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Unique value

A

asowdi

New Member
Hi all

Today I came across this issue,

on my birthday, I wanted to invite my friends, I have made group and added all my friends there, added same group in TO list of email and expanded that group to know how many are there in group. As there are many in the list, I thought to copy info to excel and count, when I did that all email id copied in single cell and made my task difficult.

One good thing is that all email IDs separated by ";" like shown below


EX: Ashwin.S; Hui.Perth; Luke.M; Faseeh.Textile; Chandoo.Aus etc...

Is there any direct function to count “;” in single cell.
 
Hi Ashwin ,


If you just want to know the number of semi-colons in the string within one cell , the following formula will do the job :


=LEN(Data_cell)-LEN(SUBSTITUTE(Data_cell,";",""))


will subtract the length of the string within the cell after stripping it of all the semi-colons , from the original length of the string. The difference will be the number of semi-colons.


This technique can be used to know the number of occurrences of any character in a string.


Narayan
 
Hi,


Just something to bear in mind.... in your example you have 5 names, using the formula only counts 4 semi-colons, so you may need to add 1 to count the names if your last name doesn't have one


EDIT: BTW Happy Birthday!
 
You must log in or register to reply here.
Back
Top