Count number of values within cell

Question

I am trying to count the number of values within a cell, in Excel. So far I have found plenty of ways to count the number of characters, regardless of whether they're a space, number, or letter. However, I would like to count the number of values in a list or a cell, like so:

Let's say in cell A1 I have the following sequence of values:

38 39 101 102 114 115 116 117 118 119 120 121 122 123 124 125

I would like for the cell to return 16, indicating that there are sixteen values in the cell.

Is there an easy way to do this with Excel?

Answer 1

If you have a known delimiter (what breaks up the values) and you don't have to test your data for whether it's a value or non-value then...

=LEN(A1)-LEN(SUBSTITUTE(A1,"[Your delimiter here]",""))+1

https://support.microsoft.com/en-us/kb/187667