Count number of comma separated values in single cell in Excel and Google Sheets

If you will have a spreadsheet containing the shares in a store or one thing related, and it’s essential depend the comma-separated values in a single cell, right here is how one can get the job achieved. There is an easy operate that you should use to place the number of all comma-separated values in a single cell with none guide work.

Let’s assume that somebody has despatched you a spreadsheet which has objects like merchandise, worth, and so on. You must make a brand new column to say what number of merchandise have reached you with the intention to clear issues up. For such instances, you should use this information.

Count number of comma-separated values in a single cell

To depend the number of comma-separated values in a single cell in Excel & Google Sheets, it’s essential observe these steps to depend the worth in a single cell-

  1. Open spreadsheet with Excel or Google Sheets
  2. Select a cell and enter a operate

The detailed information is talked about beneath.

There is an easy operate that does the job inside moments. There is not any want to put in a instrument or take assist from any third-party service or software program.

You must open the spreadsheet. You can use Microsoft Excel or Google Sheets to try this. After that, choose a cell the place you need to show the number of all comma separated values.

Then, use the next function-

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1

count number of comma separated values in single cell in excel and google sheets - Count number of comma separated values in single cell in Excel and Google Sheets

Please notice that the operate talked about above would show the number of all comma separated objects of A1 cell. You want to vary the cell number to depend the objects of a special cell.

Although this method works easily in Excel and Google Sheets, there’s a downside. You want to vary the cell number each time. That is why utilizing this operate is kind of time-consuming. However, you probably have solely ten or twenty rows, you should use this method to get your job achieved.

Leave a Comment