Want to know how many characters there are in a certain cell? This tutorial will help you choose an Excel formula for character count best suited for your particular case.
Initially, Excel was designed to work with numbers. Fortunately, the developers of this helpful application didnt forget about text. Below, youll find a number of formulas for counting characters in Excel. Just look through the examples and see which one best suits your needs.
Counting characters in an Excel cell is a common task for many spreadsheet users. Whether you need to track text lengths for social media, validate data inputs, or optimize content, understanding character counts can be crucial.
In this comprehensive guide, we’ll explore the various methods and functions to easily count characters in Excel, empowering you with the knowledge to enhance your spreadsheet skills
Why Count Characters in Excel?
Here are some common reasons you may need to leverage this skill
-
Certain form fields and databases only permit entries within a specific character range. You can use Excel to validate data before uploading to these systems.
-
Social networks like Twitter and LinkedIn restrict post lengths. Excel formulas help you conform to character limits.
-
Standardizing column lengths during data cleaning for data science requires character count knowledge.
-
Some data transmission protocols like SWIFT only allow a fixed character length. Excel helps cut unnecessary characters.
-
Counting characters in meta descriptions and tags verifies search engine optimization best practices.
-
Evaluating content lengths for articles, blogs, and product descriptions enhances readability.
-
Email marketing tools limit subject line lengths. Excel assists with maximizing open rates.
-
Tallying expected versus actual character counts validates data entry quality.
Count Characters Using the LEN Function
The LEN function is the built-in way to count characters in Excel. The formula is:
=LEN(cell)
This counts letters, numbers, spaces, and symbols in the referenced cell.
To count multiple cells, use:
=SUM(LEN(cell1),LEN(cell2),LEN(cell3))
This sums the individual LEN counts.
Count Characters in a Single Cell
-
Enter
=LEN(A2)in a blank cell, replacing A2 with your data cell. -
Press Enter to see the character count.
Count Characters in Multiple Cells
-
Copy the
=LEN(A2)formula to other result cells. -
Change the cell references to match your data range.
-
The function sums the counts.
Count Specific Characters
To tally instances of a certain letter like “z”:
-
Use
=LEN(A2)-LEN(SUBSTITUTE(A2,"z","")) -
Change “z” to the target character.
Case Insensitive Counts
To count both “Z” and “z”:
-
Use
=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"z","")) -
This lowercases the cell first.
Count a Substring
To count a substring like “AATT”:
-
Use
=(LEN(A2)-LEN(SUBSTITUTE(A2,"AATT","")))/LEN("AATT") -
Change “AATT” to your substring.
Count Characters Around a Decimal
To count characters before a decimal:
- Use
=LEN(INT(A2))
For after the decimal:
- Use
=LEN(A2)-FIND(".",A2)
Count Characters in Excel Power Query
For large datasets, Power Query is more efficient than LEN formulas.
To add data:
-
Click Data > Get Data > choose source.
-
Data loads in the Power Query editor.
To make a custom column:
-
Select Add Column > Custom Column.
-
Name it and use the formula
Text.Length([Column Name]) -
Close and load the data to Excel.
Count Characters Using Excel Power Pivot
You can also leverage Power Pivot:
-
Select your data and click Power Pivot > Add to Data Model.
-
Click the first row under the column.
-
Enter
=SUMX(Table,LEN([Column Name])) -
Create a PivotTable from the data model.
-
Add the new measure to count characters.
Count Characters Using VBA
VBA is ideal for automating character counts:
For Each cell in Range("A2:A10") If Not IsEmpty(cell) Then Destination(cell.Row-1,1) = Len(cell.Value) End If Next cellTo customize:
-
Adjust the source and destination ranges.
-
Add to a new module and run the macro.
Count Characters with Office Scripts
Office Scripts can also count characters:
let range = workbook.getRange("A2:A5") range.getOffsetRange(0,1).setFormulaLocal(`=LEN([@Column])`)To use:
-
Copy the script into the Code Editor.
-
Adjust the range.
-
Run the script.
Key Takeaways
-
Excel contains various functions like LEN, VBA, Power Query, and Office Scripts to count characters.
-
Formulas like LEN quickly tally counts in cells or ranges.
-
VBA provides automation for frequent large-scale counts.
-
Power Query handles character counting for big datasets.
-
Office Scripts deliver character counting via both desktop and web Excel.
Mastering the ability to count characters in Excel cells is a critical skill for many tasks. With the knowledge from this guide, you now have the tools to efficiently handle character counting challenges.
How to count specific characters in a cell
To find out how many times a given character appears in a cell, the generic formula is: LEN(
Suppose you maintain a database of items where each item type has its own unique identifier. And each cell contains several items separated by comma, space, or any other delimiter. The task is to get the number of occurrences of a certain unique identifier in each cell.
Assuming the list of items is in column A beginning in A3, and the target character is in column B in the same row, the formula is as follows:
=LEN(A3) - LEN(SUBSTITUTE(A3, B3, "")) 
Note. Excels SUBSTITUTE is a case-sensitive function, and therefore the above formula treats uppercase and lowercase letters as different characters. For example, cell A4 in the screenshot above contains one occurrence of “a” and two occurrences of “A”. The formula counted only the uppercase “A” and returned 2 as the result.
How this formula works:
To understand the formulas logic, lets break it down into smaller parts:
- First, you find the total string length in cell A3 with:
LEN(A3) - Then, you remove all occurrences of the letter “A” in A3 by replacing it with an empty string:
SUBSTITUTE(A3, "A", "") - The next step is to find the string length without the letter “A”:
LEN(SUBSTITUTE(A3, "A", "")) - Finally, you subtract the length of the string without “A” from the total length string:
LEN(A3) - LEN(SUBSTITUTE(A3, B3, ""))
As the result, you get the count of “removed” characters, which is the number of occurrences of that particular character in the cell.
Case-insensitive formula to count letters in Excel cell
When counting letters in Excel cells, you may sometimes need a formula that ignores the letter case. To make such a formula, use the UPPER function inside SUBSTITUTE to convert a given letter to uppercase before running the substitution.
For example, to count both “A” and “a” in cell A3, use this formula:
=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), "A", ""))
The LOWER function will also do:
=LEN(A3) - LEN(SUBSTITUTE(LOWER(A3), "a", ""))
A slightly more complex way is using nested SUBSTITUTE functions:
=LEN(A3) - LEN(SUBSTITUTE(SUBSTITUTE (A3, "A", ""), "a", "")
In our data set, the letters to be counted are input in column B, so we convert both the source cell and the cell containing the character to uppercase:
=LEN(A3) - LEN(SUBSTITUTE(UPPER(A3), UPPER(B3),""))
And this works beautifully irrespective of the target letters case: 
How to Check Character Count in Excel : Microsoft Excel Help
How to count the number of characters in a cell?
Here is the formula we will use to count the number of characters in a cell: And here is what the applied formula looks like: The LEN function has been used with a single argument; the reference to the cell for counting the characters in the cell’s text. LEN returns 76 as the number of characters in C4. This count includes space characters.
How to count all characters in a range in Excel?
And your real-life formula may look similar to this: Another way to count all characters in a range is using LEN in combination with the SUM function: Unlike SUMPRODUCT, the SUM function does not process arrays by default, so you need to press Ctrl + Shift + Enter to turn it into an array formula in Excel 2019 and earlier.
How do I get a character count in Excel?
Hit Enter to get the character counts of the cell content. Excel calculates and shows the character counts in the selected cell. Highlight a cell where you want to get a complete character count of a cell range. Modify the references (A2:A5) according to your own dataset. Hit Enter to get a character count of multiple cells.
How do you count characters in a sentence?
Count characters in one cell Click cell B2. Enter =LEN (A2). The formula counts the characters in cell A2, which totals to 27—which includes all spaces and the period at the end of the sentence. NOTE: LEN counts any spaces after the last character. Count characters in multiple cells Click cell B2.
