Formula to count values in single cell separated by commas

[aimeecrystalaid]

Is it possible to have a formula that can count the amount of values in a single cell?

EG:

Cell A2 contains "C1, C2, C3, D1, D4, D5"
is there a formula i can put in Cell B2 that will count how many values there are in A2?

so cell B2 would then say "6"

Every value is separated by a comma and a space exactly as above (if that helps)

thanks!

[AlKey]

Please try this formula:

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

[RobertMika]

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1
or
LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),",",""))+1
(in case of trailing or leading spaces)

[aimeecrystalaid]

Thanks both of you! i thought i had marked this as solved and had replied but it appears to not have worked?

Thanks Robert, in my case there will definitely not be any trailing or leading spaces because the step beforehand is a macro that sets up the values in this way.

either way, they both work, thanks!

[RobertMika]

You are weclcome.
Reputation will be welcomed as well.

[eeijas]

OMG thanks for this!

[Dhrubo]

Most appropriate would be
=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),",",""))+1)

[Tenjika]

I have a similar item, but the values are separated by a semicolon. The final value also has a semicolon, which is adding an extra digit to the sum. Any help? Thanks!

[protonLeah]

Tenjika,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.