If in case you have a spreadsheet containing the shares in a store or one thing related, and you have to rely the comma-separated values in a single cell, right here is how one can get the job performed. There’s a easy operate that you should utilize to place the variety of all comma-separated values in a single cell with none guide work.
Let’s assume that somebody has despatched you a spreadsheet that has gadgets like merchandise, costs, and so on. You’ll want to make a new column to say what number of merchandise have reached you as a way to clear issues up. For such instances, you should utilize this information.
Count variety of comma-separated values in a single cell
To rely the variety of comma-separated values in a single cell in Excel & Google Sheets, you have to observe these steps to rely the worth in a single cell-
- Open spreadsheet with Excel or Google Sheets
- Choose a cell and enter a operate
There’s a easy operate that does the job inside moments.
You’ll want to open the spreadsheet. You should utilize Microsoft Excel or Google Sheets to do this. After that, choose a cell the place you need to show the variety of all comma-separated values.
Then, use the next function-
=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),",",""))+1
Please notice that the operate talked about above would show the variety of all comma-separated gadgets of A1 cell. You’ll want to change the cell quantity to rely the gadgets of a completely different cell.
Though this method works easily in Excel and Google Sheets, there’s a disadvantage.
You’ll want to change the cell quantity each time. That’s the reason utilizing this operate is kind of time-consuming. Nevertheless, when you have solely ten or twenty rows, you should utilize this method to get your job performed.
Learn: The way to rely Nonblank Cells in Excel.
