Share via

Count number of commas in a cell

Anonymous
2020-04-10T16:04:45+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

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.

0 comments No comments

Answer accepted by question author

HansV 462.6K Reputation points
2020-04-10T16:07:32+00:00

In B1:

=IF(A1="",0,LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)

Fill down.

Was this answer helpful?

50+ people found this answer helpful.
0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Anonymous
    2020-04-10T17:57:22+00:00

    Brilliant!!  thank you

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments
  2. Anonymous
    2020-04-10T16:10:18+00:00

    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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments No comments