Contextures News

Excel Count Items in a Cell

January 31, 2017

How to count items in a cell, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Count Items in a Cell

My daughter called for some Excel help last week -- I love when that happens! She had a worksheet with multiple names in cells, separated by commas, and she needed to count the names in each cell.

How could you get Excel to count the names for her? My solution is below the screen shot, so scroll down when you want to see the answer.

NOTE: If you want to allow multiple selections from a drop down list, there's a tutorial and sample file on my website

multiple names in cells

To count the names in cell B2, I used 3 formulas -- you could combine them into one formula.

  • In cell C2, find the length of the list: =LEN(B2)
  • In cell D2, find the length, with commas removed: =LEN(SUBSTITUTE(B2,",",""))
  • In cell E2, subtract D2 from C2, to get the number of commas. Add 1 for the last name, which doesn't have a comma: =C2-D2+1

Read more about the SUBSTITUTE function, and see how LEN and SUBSTITUTE can be used together for data validation.

Excel Dashboard Course Giveaway

Deadline 4 PM today -- Go to my Contextures blog, and enter the Excel Dashboard Pro giveaway. It's easy to enter -- just think of a unique hashtag that you'd use to get help with a dashboard problem.

The grand prize is free registration for new Excel Dashboard Pro course, from Excel TV. There are other prizes too, for runners up, and for my favourite hashtags. Read the blog post and the rules, to get all the details. Don't delay though -- the deadline is 4 PM today, January 31, 2017 (Toronto - Eastern Standard time).

And if you don't win the grand prize, you can get 20% off the course, with coupon code contextures20. The free Dashboard video series is still available too.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Excel Charts - Jon Peltier shows how to connect chart points with arrows, with or without a macro. If you build lots of charts, check out Jon's Excel chart utility. (Level - Intermediate/Advanced)

Excel Formula - In a video, Mike Girvin (ExcelIsFun) shows how to format or extract every nth item in a list. Watch how he builds the formulas, and download his sample workbook. (Level - Intermediate)

Excel Training & Events - Don't miss out! See a list of upcoming Excel courses, free webinars and other Excel Events. Find online events, and on-site events in Canada, UK and Amsterdam.

Also, see all my Excel products on my Contextures website, and my weekly collection of humorous Excel tweets. For example, "Dear Excel, please run faster, I have work to do."

The Giant Baby

Yesterday was my son's birthday, so I dug out some baby photos. We enjoyed that baby backpack, but he outgrew it very quickly. He had an impressive start -- 11 lb 4 oz at birth, and he's 6'4" now.

I won't say how long ago it was, but the green fridge, and my giant eyeglasses, might give you a clue.

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.


Last updated: May 24, 2021 8:02 PM