Skip to main content

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.

Before applying Kutools for Excel, please download and install it firstly.

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:

  If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
To not show blank cells as 1, add an if statement using ISBLANK in the condition:=IF(ISBLANK(A1),0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1)

or, if you prefer to avoid IF logic blocks in your sheet, use SIGN and LEN:=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+SIGN(LEN(A1))


This comment was minimized by the moderator on the site
It returns a count of 1 from an empty cell. How do you fix this so it returns the count of zero??
This comment was minimized by the moderator on the site
what if some of the cells only have one value, e.g. cell A = KTW, cell B = VAL, SME how to count cell A?
This comment was minimized by the moderator on the site
How To Count Comma Separated Specific Values in a single cell In Excel?
Suppose to count how many 15's are there in a single cell that contains 10,1,15,20,15,5,15,155
I need the result 3.
Is there any formula in excel
This comment was minimized by the moderator on the site
This worked great for me but i had to edit formula for Office 2016. (TRIM(A1),",","")) replaced with (TRIM(A1);",";""))
This comment was minimized by the moderator on the site
Dear Shandoo,

Thank you for your supplement!
This comment was minimized by the moderator on the site
i have comma separated values in cells like A=1,2,3,4,5,6 B=3,4,5,6,7,8 I want to add the contents of the two cells without repeating the value and also want to get the common value of two cells expected result = 1,2,3,4,5,6,7,8 =3,4,5,6 i am not getting the exact formula in microsoft excel 2013 Kindly help me Thanks
This comment was minimized by the moderator on the site
have you found out the way to achieve this? i am looking for exactly the same thing. thanks
This comment was minimized by the moderator on the site
i too looking for the same
This comment was minimized by the moderator on the site
What if some of the cells underneath are blank? When using the fill handle on the rest of the cells it's coming up with 1 as the total.
This comment was minimized by the moderator on the site
Hello James55, don't know if you ever got an answer but in case you need one. Use an IF statement to check if the cell is blank and then print a "0" or null "" whichever you like. =IF(A1="","0",(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+"1"))
This comment was minimized by the moderator on the site
Great share info
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations