How to Count Number of Characters in a Cell in Excel (Easiest 6 Ways)

If you are looking for some of the easiest ways to count a 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 in doing it manually. So, let’s get into the article to know the ways of making this task easier.

Download Workbook

Easiest 6 Ways to Count Number of Characters in a Cell in Excel

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

data set

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

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

=LEN(text)

Here, C4 is text.

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

Read more: Count Specific Characters in a Column in Excel: 4 Methods

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(C4:C9)))

Here, C4:C9 is the range of characters.

sum of characters in a range

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

sum of characters in a range

Method-3: Counting Numbers in a Cell

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

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

Here, the SUBSTITUTE function is used to omit the numbers in cell C4, 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.

counting numerical characters

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

count numerical values in a cell

Read More: Count Numbers in a Cell in Excel (3 Methods)


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(C4)-(SUM(LEN(C4)-LEN(SUBSTITUTE(C4,{0,1,2,3,4,5,6,7,8,9},""))))

count characters except numerical values

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

count characters except numerical values

Read More: Count the Number of Specific Characters in a Cell in Excel (2 Approaches)

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(C4)-LEN(SUBSTITUTE(C4,"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 C4, the old text is “a” and the new text is Blank

count special character

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

count special character

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

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

vba code to count character

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(pInput As String) As String

Dim xRegex As Object

Dim xMc As Object

Dim xM As Object

Dim xOut As String

Set xRegex = CreateObject("vbscript.regexp")

xRegex.Global = True

xRegex.ignorecase = True

xRegex.Pattern = "[^\w]"

CharacterNo = ""

If Not xRegex.test(pInput) Then

    xRegex.Pattern = "(\d+|[a-z]+)"

    Set xMc = xRegex.Execute(pInput)

    For Each xM In xMc

        xOut = xOut & (xM.Length & IIf(IsNumeric(xM), "N", "L"))

    Next

    CharacterNo = 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.

vba code to count characters

Step-04: Then in Cell D4 write the function CharacterNo and insert the text in C4.

=CharacterNo(C4) 

count character

Step-05: After pressing ENTER and dragging it down the following results will appear.
Here, L denotes any character except number and N denotes 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 characters in a cell

Read More: Excel VBA: Count Characters in Cell (5 Methods)

Conclusion

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


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo