A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
In B1:
=IF(A1="",0,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)
Fill down.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello,
I need an Excel function that looks into a cell and depending on its content, puts in a number in the cell to the right of it. So the criteria is that if the cell is blank, returns 0 in the next column over, if the cell does not contain a comma in it, returns 1, if there is one comma in the cell, returns 2, if there are two commas in the cell, returns 3, and if there are three commas in the cell, then returns 4....
For example
col A col B would return the following number
1 MBA 1
2 MD 1
3 MBA,MD 2
4 MBA,MD,Ph.D 3
5 0
6 Ph.D 1
7 0
8 MD,Ph.D 2
9 MBA,MD,MS,Ph.D 4
10 MS 1
11 MBA,Ph.D,MS 3
Thank you for any assistance
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
In B1:
=IF(A1="",0,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)
Fill down.
Brilliant!! thank you
Hi CraigBrody,
I'm an Independent Advisor and consumer of Microsoft products just like you. Let me help you with your concern.
Refer to the link below to count any character/s in a cell.
https://exceljet.net/formula/count-specific-cha...
Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.