Wondering how to count characters in Excel? Read this to learn all the popular ways of counting characters in Excel via real-world examples that you can put to business or personal use.

Counting characters in Excel is a fundamental skill for anyone working with spreadsheets, whether you’re a data analyst, a student, or a business professional.

Understanding how to tally the number of characters in a cell can be crucial for various tasks, such as tracking text length for social media posts, monitoring data input limits, or preparing concise reports.

In this comprehensive guide, I’ll delve into the various methods and functions available within Excel to effortlessly count characters, offering you the knowledge and tools needed to enhance your spreadsheet proficiency.

Reasons to Count Characters in Excel

Here are the reasons why you might need to utilize this skill in your business or profession:

1. Preconfigured form fields and database inputs, at times, only allow you to enter content or numbers within a specific character limit. Now, if you’re uploading content to a database at millions of entries level, you can use Excel to calculate the character counts of the content and make necessary changes before uploading.
2. Various social media and microblogging sites limit the number of characters you can type in a comment, message, reply, or post. If you engage with friends and family members a lot via social media, you can use the formulas mentioned in this article to count characters before submitting your posts.
3. If you’re posting social media content at a business level for many clients, make your workflow efficient by counting the characters of the posts on Excel so you can reduce reworks.
4. During the data cleaning process for data science and machine learning, you might discover that some features (columns) are excessively long horizontally because of the observation (rows) data. If you must standardize the size of the column length horizontally, the character counting skill of Excel will help.
5. SWIFT and other data transmission systems allow only a few characters length of text. Here, you can cut down unnecessary characters by counting characters in Excel.
6. Also, you can analyze meta tags and descriptions to ensure they adhere to search engine character restrictions.
7. You might want to gauge content length for articles, blog posts, or product descriptions to enhance readability and SEO performance.
8. This Excel skill helps you to stay within email client character limits for subject lines and preheaders to maximize open rates.
9. You can ensure that your data entry agents are properly inputting data into the forms by calculating total characters and tallying that with the character count you expected.

Counting Characters in Excel Using LEN Function

The built-in function to calculate characters of text strings or numbers in Excel is the LEN function. It’s super-easy to use this function. Simply, enter the function after an equal sign and put the cell reference or text in a parenthesis.

The formula instantly tells you the total character count of the cell irrespective of the content type, like symbols, spaces, numbers, and texts.

However, this simple solution might not help you directly. You need to modify the formula in various ways. Find below some common ways to count characters in Excel using the LEN and its modified functions.

Count Characters in a Single Cell

1. Enter the following formula into a cell where you want to get character counts of reference cells:

=LEN(A2)

2. In the formula, don’t forget to change the cell reference according to your dataset.
3. Hit Enter to get the character counts of the cell content.
4. Excel calculates and shows the character counts in the selected cell.

Count Characters in a Range of Cells

1. Highlight a cell where you want to get a complete character count of a cell range.
2. Enter the following formula into the cell’s Formula Bar:

=SUMPRODUCT(LEN(A2:A5))

3. Modify the references (A2:A5) according to your own dataset.
4. Hit Enter to get a character count of multiple cells.

Count Specific Characters

Suppose you want to know how many Zs are there in a content or text string. Do you manually count Zs in the content?

Absolutely, not! You input the text string or content into a cell in your Excel worksheet. Then, use the following formula in an adjacent cell:

=LEN(A2)-LEN(SUBSTITUTE(A2,"z", ""))

The above formula expects that the content being counted is in the cell A2 and the character you’re counting is the small Z within quotes.

Creating a Case-Insensitive LEN Formula

If you want to count all small and capital letters of Z, use the following case-insensitive formula instead:

=LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"z", ""))

Here’s how you can modify the formula to make them work for you:

• A2 should be the reference cell that contains the content, texts, or numbers being calculated.
• The code element "z" tells Excel which alphabet or character to count. So, change this as well according to your needs.

Counting a Substring in a Cell

Sometimes, you need to count a specific substring in a long text, code, or content. You can do that using the following steps:

1. Highlight the cell where you want to get the total count of a substring in a long text or number string.
2. Enter the following formula into the cell:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"AATT", "")))/LEN("AATT")

3. Hit Enter to get the character count instantly.

You can convert the above formula to take relative values from cell references for ease of work. Here’s a modified version of the same formula:

=(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2)

Here’s how to customize this formula:

• Enter the content cell or cell range reference in place of A2.
• Similarly, replace B2 with your own cell reference or range for the substring to be counted.

Count Characters After and Before a Decimal

Suppose, the content in a cell has a decimal as the delimiter. Now, you want to count how many characters are there before and after the decimal place.

In this situation, you can use the following Excel formula and steps:

1. Highlight a cell and enter this formula into it:

=LEN(INT(A2))

2. Hit Enter to know how many numericals are there before the decimal point.

Now, to calculate the digits after the decimal point, use this modified LEN formula:

=LEN(A2)-FIND(".",A2)

In both the above formulas, A2 is the cell reference for the content being counted. So, modify the formula accordingly when using it in your own worksheet.

Count Characters in Excel in Power Query

So far, you’ve gone through various LEN functions to count characters in Excel. If you need to scale up the process for millions of entries, the LEN function isn’t an efficient choice.

You must use Power Query! Also, if you’re importing the strings or content for character counting from an external database, you’d use Power Query for the data import.

While importing, you can transform your data in Power Query, extract character counts, and add that to the worksheet directly. Find below the Power Query formulas and steps you must try.

There are two ways to add a dataset to Power Query. The first one is using third-party data sources. Here’s how:

1. Click the Data tab on the Excel ribbon menu.
2. Go to the Get & Transform Data commands section and click Get Data.
3. Hover the cursor over the database platform like From Azure.
4. Select the data container on the overflow menu, like From Azure SQL Database.
5. Power Query will open and you should find tabulated data inside the tool.

Another way to inject data into Power Query is from your existing Excel worksheet. Here’s how to do that:

1. Highlight the dataset you want to import to Power Query.
2. Click the Data tab.
3. Click From Table/Range on the Get & Transform Data section.

4. Click OK on the Create Table dialog box.
5. The dataset will open on the Power Query Editor.

After importing an external or local worksheet dataset to Power Query, you must apply a formula to count the characters in the input dataset. Here’s how it’s done:

1. Go to the Power Query Editor window.
2. Click the Add Column tab.
3. Click Custom Column inside the General commands block.
4. On the Custom Column dialog box, enter a name for the new column.
5. Inside the Custom column formula field, enter the following formula:

Text.Length([Content])

6. Click OK to create the new column with a formula.

7. The new column should show the character counts.
8. Click the File tab and choose the Close & Load To option.

9. On the Import Data dialog, select Existing worksheet.
10. Also, highlight the area where you want the data from Power Query to be imported.
11. Click OK to complete the import process.

The above image shows the way Excel imports data to the existing worksheet. You can now change the table style to reformat your dataset.

Counting Characters in Excel in Power Pivot Measure

Find below the steps for counting characters in Excel using Power Pivot Measure:

1. Highlight the source dataset and click the Power Pivot tab.
2. Click the Add to Data Model button.
3. Click OK on the Create Table dialog.
4. You should now see the dataset inside the Power Pivot tool.
5. Click the first row below the first column where you see your data.
6. There, enter the following formula:

=SUMX(Table5,LEN([Content]))

7. Hit Enter and Excel will create a new measure for you.

Now, find below how to use the Power Pivot Measure in a PivotTable in Excel:

1. Click the PivotTable button on the Power Query ribbon.

2. On the Create PivotTable dialog, select Existing Worksheet and click OK.

3. You should now see the PivotTable.
4. Expand the table you started with, Table6 in this tutorial, on the PivotTable Field panel.
5. Checkmark Measure 5.
6. You should see the sum of characters in the selected cell ranges.

Count Characters in Excel Using VBA

Excel VBA enables you to fully automate the character counting process in Excel. Find below a script that will count the characters in a cell range A2:A10 and put the results in B2:B10:

Sub CountCharacters()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim cell As Range    
  
    Set ws = ThisWorkbook.Sheets("Sheet2")   
    Set sourceRange = ws.Range("A2:A10")   
    Set destinationRange = ws.Range("B2:B10")   
    For Each cell In sourceRange
        If Not IsEmpty(cell.Value) Then           
            destinationRange.Cells(cell.Row - 1, 1).Value = Len(cell.Value)            
        Else           
            destinationRange.Cells(cell.Row - 1, 1).Value = 0
        End If
    Next cell
End Sub

Here’s how you can use this script on your worksheet:

1. Press Alt + F11 to bring up the Excel VBA Editor.
2. On the menubar, you shall find the Insert menu. Click on it.
3. A context menu will show up with a few options. Click on Module.
4. On the new module, copy and paste the above script.
5. Here are the customizations you can do:
   • Sheet2 should be replaced with the actual worksheet name
   • A2:A10 should be replaced with the input data source on your sheet
   • B2:B10 also needs to be changed if you want the character counts in another cell range
6. Click the Save button to save the script.
7. Click Close to close the Excel VBA Editor.

So far, you’ve created a VBA Macro named CountCharacters. Here’s how to run it on your worksheet:

1. Press Alt + F8 to call the Macro dialog box.
2. Select the macro you want to run.
3. Hit the Run button.

Excel should count the characters of the source cell range and populate the results in the destination cell range.

Find below another Excel VBA script that will count a specific character in the given cell or cell range containing strings, numbers, or special characters:

Sub CountSubstringOccurrences()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim resultRange As Range
    
    ' Set the worksheet where you want to perform the calculation
    Set ws = ThisWorkbook.Sheets("Sheet3") ' Change "Sheet1" to your desired sheet name
    
    ' Define the source range (Column A)
    Set sourceRange = ws.Range("A2:A10") ' Adjust the range as needed
    
    ' Define the result range (Column C)
    Set resultRange = ws.Range("C2:C10") ' Adjust the range as needed
    
    ' Loop through each cell in the source range
    For Each cell In sourceRange
        ' Check if the cell is not empty
        If Not IsEmpty(cell.Value) Then
            ' Calculate the ratio of occurrences of the substring in column B to the total length of column B
            cell.Offset(0, 2).Value = (Len(cell.Value) - Len(Application.WorksheetFunction.Substitute(cell.Value, cell.Offset(0, 1).Value, ""))) / Len(cell.Offset(0, 1).Value)
        Else
            ' If the cell is empty, clear the corresponding result cell
            cell.Offset(0, 2).Clear
        End If
    Next cell
End Sub

Find the dataset structure and results in the given image. You can customize the script by following the comments in it.

Counting Characters in Excel Using Office Scripts

Since Office Scripts works on both the Excel desktop app and Excel on the Web, you can also try this tool to count characters. Also, you can further automate tasks on Excel-like emailing to a client using Power Automate and Office Scripts.

Find below the script that counts specific characters in a range of cells and populates the results in adjacent columns:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range C2 on selectedSheet
	selectedSheet.getRange("C2").setFormulaLocal("=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,B2,\"\"),LOWER(B2),\"\"))");
	// Auto fill range
	selectedSheet.getRange("C2").autoFill("C2:C5", ExcelScript.AutoFillType.fillDefault);
}

To use this script, follow these steps:

1. Click the Automate tab.
2. Click New Script.
3. Copy and paste the above script into the Code Editor interface.
4. Click Run.

Conclusions

Mastering the art of character counting in Excel is an invaluable skill for a multitude of tasks, from data validation to content optimization.

With various techniques at your fingertips, such as Excel LEN function, Power Query, Power Pivot Measure, VBA scripts, and Office Scripts, you’re equipped to efficiently handle character-related challenges in your spreadsheets.