Excel Formula for counting a specific character in a Cell?

bleggee used Ask the Experts™
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:
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?
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
Assuming your test string is in A2 and you are trying to count the occurrences of & in the text string, you may try this...

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

Click the button in this workbook.


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

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