Number of values seperated by commas in a cell

[Ghris]

Heya,

I got a problem to solve: In my cell (O5) I have an amount of numbers, separated by commas and spaces. I wish to know the amount of numbers in that cell.
For example: This is in O5: "1, 3, 8, 12, 15, 16" (without the "") And i need something that tells me that there are 6 values/numbers in there.

Restrictions:
Doesn't matter if its VBA or a formula tells me that. But preferably I don't want to use a text to columns macro. Also preferably not with variables, as I will count the amount of values in that cell thousands of times.

Kind regards,

Chris

 

[VoG]

Try

=LEN(O5)-LEN(SUBSTITUTE(O5,",",""))+1

 

[jim may]

How about

Excel 2012

	A	B
1, 3, 8, 12, 15, 16

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"]6[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]B1[/TH]
[TD="align: left"]=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Ghris]

Hmm, both the same answer, but it doesn't seem to work. Excel gives me an error, he thinks im not trying to type a formula. It knows LEN function tho... weird

 

[VoG]

Try copying the formula from here and pasting into a cell.

 

[Ghris]

Tried copying and typing. Both give this error:

My actual cell for checking is S9 instead of O5 But that can't be the problem I assume.

Edit: this is what insert function gives as invalid: S9,",",""

 

[VoG]

It works perfectly happily here

Excel Workbook
	Q	R	S
9	7		1, 3, 8, 12, 15, 16, 23
Sheet6

 

[Jonmo1]

Perhaps you need to use semicolons instead of commans?

=LEN(S9)-LEN(SUBSTITUTE(S9;",";""))+1

 

[Ghris]

Doh! Jonmo1 is right on the semicolons. Thanks all for the help! Much appreciated. Must be something regional?

 

[Jonmo1]

Glad to help.

Yes, it's a regional setting called the "List Seperator". Found in Windows Control Panel - Region And Language - Additional Settings.