Excel Formula for counting a specific character in a Cell?

bleggee
bleggee used Ask the Experts™
on
I want to count how many occurrences of a certain character exist in a column of Excel data.
For example, if I want to count Ampersands (&) and I have this:
        A
Cars & Trucks & Engines
Cars & Trucks
I want to end up with this
        A                                               B
Cars & Trucks & Engines              2
Cars & Trucks                                  1

Is there a formula (or another method) that can do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Assuming your test string is in A2 and you are trying to count the occurrences of & in the text string, you may try this...

=LEN(A2)-LEN(SUBSTITUTE(A2,"&",""))
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Click the button in this workbook.
29166920.xlsm

Author

Commented:
Awesome, you both Rock! Thank you
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial