How to count comma separated values in a single cell in Excel?
If cell content is separated by comma in a single cell, such as “A1, A2, A3, A4, A5”, and you want to count the total number of comma separated values in this cell, what can you do? In this case, the total comma separated values should be 5. In this article, we will show you two methods of counting comma separated values in a single cell in Excel.
Count comma separated values in a single cell with formula
Easily count comma separated values in a single cell with an amazing tool
More tutorials for counting in Excel
Count comma separated values in a single cell with formula
Take the data in below screenshot as an example, please do as follows to count comma separated values in each cell of range B3:B7.
1. Select a blank cell to output the result.
2. Type the below formula into it and press the Enter key. Selec the result cell and then drag its Fill Handle down to get all results.
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1
Easily count comma separated values in a single cell with an amazing tool
With the Count the number of cells separated by comma utility of Kutools for Excel, you can easily count comma separated values in a cell in Excel without applying formula manually.
1. Select a blank cell to output the result.
2. Go to the Kutools tab, click Formula Helper > Formula Helper.
3. In the Formulas Helper dialog box, please configure as follows.
- Find and select Count the number of values separated by comma in the Choose a formula box;
Tip: you can check the Filter box, type in certain words to filter the formula names. - In the Cell box, select the cell in which you want to count comma separated values;
- Click the OK button. See screenshot:
4. Now the result is populated into the selected cell. Select this result cell, and then drag its Fill Handle down to get other results. See screenshot:
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Related Articles:
Count Duplicate Values In A Column In Excel
If you have a list of data in a worksheet with unique values and duplicate values, and you do not only want to count the frequency of duplicate values but also want to know the order of the occurrence of the duplicate values. The methods in this article can do you a favor.
Count Number Of Cells With Text Or Number In Excel
For example you have a worksheet mix with texts and numbers, and you want to count the total cell numbers of texts and numbers separately, what would you do? In Excel, you can use formula to achieve this. Please browse this tutorial for more details.
Count If Cell Contains Text Or Part Of Text In Excel
Supposing you have the below data, and want to count the number of cells which contain the text "Apple", number of cells contain the text "Orange" and cells contain "Peach" separately, how to get it done? In this tutorial, we explains the COUNTIF function in detail to help you quickly count the number of cells if they contain text or part of specific text in Excel. Besides, we introduce an awesome feature to acheive it easily with only clicks.
Count Number Of Cells Between Two Values Or Dates In Excel
If you need to count cell numbers that contain specific values between two numbers or dates, the formula of the CountIf function can quickly help you. In this tutorial, we will show you details of counting number of cells that between two values or dates in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in