Get Count Comma Separated Numbers in a Cell in Google Sheets-CodePudding

We have data of comma-separated values with no comma at the start and end in a cell and we are trying to get the number of times an occurrence is in the cell. So in the example, the next cell is checking for 18 with the value of 2.

18,9,8,15,18,118 | 2

We are trying this

=IF(ISBLANK(A2),0,COUNTA(SPLIT(A2,"18")))

We have made the cell plain text which checks for no value then splits the cell to look for 18 but we are getting odd numbers due to the comma not being on the final number and also counting 118 which is not the unique value.

This returns a value of 4

CodePudding user response：

Try

=arrayformula(countif(REGEXEXTRACT(to_text(split(A1,",")),"\d "),"18"))