How to Count Number of Characters in a Cell in Excel?

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some of the easiest ways to count the number of characters in a cell in Excel, then you are in the right place. Sometimes it may become necessary to count the character number in a cell but it becomes tedious and ineffective to do it manually. So, let’s get into the article to know the ways of making this task easier.


How to Count Number of Characters in a Cell in Excel: 6 Ways

In the following table, I have a column with the name Password where different passwords are available in each cell. For meeting the requirement of a strong password it is necessary to meet the requirement of a password limit. For checking out if it is meeting the requirements I will show various ways to count the character number of the Password here.

excel count number of characters in a cell


Method-1: Counting Characters in a Cell Using Excel LEN Function

Step-01: To count the number of characters in a cell you will need to use the LEN Function in Excel here.

=LEN(text)

Here, D5 is text.

excel count number of characters in a cell using len function

Step-02: After pressing ENTER and dragging it down the following results will appear.

excel count number of characters in a cell using len function

Read More: How to Count Specific Characters in a Cell in Excel 


Method-2: Counting SUM of All Characters in a Range

Step-01: To find the sum of all characters in a range you have to use the LEN function within the SUM function.

=SUM((LEN(D5:D10)))

Here, D5:D10 is the range of characters.

excel count number of characters in a cell in a range

Step-02: After pressing ENTER you will have the Sum of the characters in the range you wanted.

excel count number of characters in a cell in a range

Read More: How to Count Specific Characters in a Column in Excel


Method-3: Counting Numbers in Excel Cell

Step-01: If you want to count how many numbers exist in a text (i.g. password) then type the following formula.

=SUM(LEN(D5)-LEN(SUBSTITUTE(D5,{0,1,2,3,4,5,6,7,8,9},"")))

Here, the SUBSTITUTE function is used to omit the numbers in cell D5, and then the newly formed password’s character number will be counted by using the LEN function.
After that, it will be subtracted from the old character number and then the result will be summed up.

Step-02: After pressing ENTER and dragging it down you will get the total number of numerical values in a cell.

Read More: How to Count Alphabet in Excel Sheet


Method-4: Counting Characters in a Cell Except for Numbers

Step-01: If you want to count the characters in a cell except for numbers then you have to subtract the total character number in a cell from the number of numerical values in a cell(which we got in Method-3).

=LEN(D5)-(SUM(LEN(D5)-LEN(SUBSTITUTE(D5,{0,1,2,3,4,5,6,7,8,9},""))))

Step-02: After that, you have to press ENTER and drag it down and then the number of characters except numbers will appear.

Read More: How to Count Characters in Cell Including Spaces in Excel


Method-5: Counting Special Characters in a Cell

Step-01: If you want to count any special character in a cell then use the following formula.

=LEN(C5)-LEN(SUBSTITUTE(C5,"a",""))

Here, the total character number will be subtracted from the character number where a special character like “a” is used.

=SUBSTITUTE(text,old text,new text)

Here, the text is C5, the old text is “a” and the new text is Blank.

Step-02: After pressing ENTER and dragging it down you will get the following result.

Read More: How to Count Characters in Cell without Spaces in Excel


Method-6: Counting Characters in a Cell Using VBA Code

Step-01: At first you have to follow Developer Tab>>Visual Basic

Step-02: Then Visual Basic Editor will appear and then go to Insert >>Module.

insert module

Step-03: Then Module 1 will be created and here you will write the following code.

Function CharacterNo(Sh_pInput As String) As String
Dim Sh_xRegex As Object
Dim Sh_xMc As Object
Dim Sh_xM As Object
Dim Sh_xOut As String
Set Sh_xRegex = CreateObject("vbscript.regexp")
Sh_xRegex.Global = True
Sh_xRegex.ignorecase = True
Sh_xRegex.Pattern = "[^\w]"
CharacterNo = ""
If Not Sh_xRegex.test(Sh_pInput) Then
Sh_xRegex.Pattern = "(\d+|[a-z]+)"
Set Sh_xMc = Sh_xRegex.Execute(Sh_pInput)
For Each Sh_xM In Sh_xMc
Sh_xOut = Sh_xOut & (Sh_xM.Length & IIf(IsNumeric(Sh_xM), "N", "L"))
Next
CharacterNo = Sh_xOut
End If
End Function

After writing this code, Save this code and Close the window.

Here, a function named CharacterNo will be created and you can change the name as per your wish.

Step-04: Then in Cell E5 write the function CharacterNo and insert the text in D5.

=CharacterNo(D5) 
Step-05: After pressing ENTER and dragging it down the following results will appear.
Here, L denotes any character except a number and N denotes a numerical character.
Let’s take the first cell 1L1N3L2N2L which represents (1+3+2)L or 6L or 6 characters except for number and (1+2)N or 3N or 3 numerical characters.

Count Number of Characters in a Cell in Excel: Knowledge Hub


Download Practice Workbook


Conclusion

In this article, I tried to cover the easiest ways to count the number of characters in a cell. I hope this article will help you regarding this topic. If you have any further suggestions, feel free to share them with us. Thank you.


<< Go Back to String Manipulation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo