Alex Campbell
asked on
How to create a user function to count for a particular characters in a cell
I had this question after viewing Count specific characters in a cell or range in Excel.
I need the function to be available for all my spreadsheets.
I need the function to be available for all my spreadsheets.
ASKER
I want the function be available in all the spreadsheets without re-entering the function each time.
Even if this were deployed as a UDF, you would still have to write a formula using that UDF in all of your workbooks.
Going the UDF route only makes sense if the functionality you need is not readily available through the built-in functions.
Going the UDF route only makes sense if the functionality you need is not readily available through the built-in functions.
Patrick is right here.
Even the link you provided offered a inbuilt formula based solution which you can use in any of your workbooks.
But if for any reason you want to use a UDF, you may place the following code on a Standard Module of a blank workbook and save it as an Excel Add-in (.xlam) and then enable that Add-in from the Developer Tab --> Add-Ins.
Even the link you provided offered a inbuilt formula based solution which you can use in any of your workbooks.
But if for any reason you want to use a UDF, you may place the following code on a Standard Module of a blank workbook and save it as an Excel Add-in (.xlam) and then enable that Add-in from the Developer Tab --> Add-Ins.
Function CountCharacters(cell As Range, Character As String) As Long
CountCharacters = Len(cell.Value) - Len(Replace(cell.Value, Character, ""))
End Function
Then you can use the above function on the worksheet as below....=CountCharacters(A1,"character to count")
ASKER
I have gone through the steps several times, but I must be doing something wrong. While it shows up in the Add-Ins list, it doesn't show up when I start typing the function name.
"But if for any reason you want to use a UDF, you may place the following code on a Standard Module of a blank workbook and save it as an Excel Add-in (.xlam) and then enable that Add-in from the Developer Tab --> Add-Ins."
"But if for any reason you want to use a UDF, you may place the following code on a Standard Module of a blank workbook and save it as an Excel Add-in (.xlam) and then enable that Add-in from the Developer Tab --> Add-Ins."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both the answer and video were very good. I was not familiar with Inserting a Module.
You're welcome Alex! Glad to help.
If you want to know how many instances of the letter "o" occur in A1:
=LEN(A1)-LEN(SUBSTITUTE(A1
If you want to know how often the letter "o" occurs in the range A1:A3, use this array formula:
{=SUM(LEN(A1:A3)-LEN(SUBST
To enter an array formula, DO NOT type the curly braces, and use Ctrl+Shift+Enter instead of Enter to finish the formula.