I have previously covered a formula that counts specific characters. Since it used multiple functions, I thought I would simplify it by creating a custom function.
In the image below I have a text string in cell A1. I want to count how many times the letter c appears in that text string. I have entered c in cell B1.
The formula in cell D1 is.
=LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))The formulas in column C are displayed in column D using the FORMULATEXT function.
This works by calculating how many characters in cell A1 and subtracting the length of the SUBSTITUTE which removes the character from cell A1. The difference between the two is how many times the character appears.
I’ve been creating a lot of custom functions recently, if you are not familiar with LAMBDA custom functions then please check out this introductory blog post.
The first step in creating a custom function is to create the LAMBDA function which we can test in a cell. I have created that formula in cell D2.
This accepts two arguments.
ref – a cell reference.
txt – the text to count.
These arguments are passed to the formula to calculate the result which the LAMBDA displays.
We use a range name to create the custom function.
Copy the LAMBDA formula without the extra set of brackets on the end – see below for what to copy.
=LAMBDA(ref,txt,LEN(ref)-LEN(SUBSTITUTE(ref,txt,"")))Click on the Formulas ribbon tab and click the Define Name icon.
In the Name box at the top enter fnCOUNTTEXT
In the Refers to box paste the formula. See image below
That’s it – the custom function is ready to use.
I have used it in cell C3.
=fnCOUNTTEXT(A1,B1)As you can see it is shorter and easy to use. I use the fn prefix for custom functions to differentiate them from normal Excel functions.
You can use the custom function throughout the file.
If you copy the formula to another file the name and custom function is also copied across.
Please note: I reserve the right to delete comments that are offensive or off-topic.