How to Count Characters in Microsoft Excel Cells

Counting characters in an Excel cell is a lot easier than you might think. With a few simple formulas, you can count the number of characters in a single cell or multiple cells as well as get a total of specific characters.

If you’re preparing to export your data and must limit the characters per cell or want to find the number of occurrences of a particular character, here’s how in Microsoft Excel.

Count Characters in a Single Cell in Excel

To get the total number of characters in a cell, you’ll use the Excel LEN function. The syntax for the formula is simply LEN(cell), where you enter a cell reference for the argument.

As an example, you can count the characters in cell A2 using this formula:

=LEN(A2)

Remember that both punctuation marks and spaces count as characters, as do any spaces after the last character in the string.

Tip: You can also count the number of bytes in a cell using the LENB function. For instance, use =LENB(A2) for the above example.

Count Characters in Multiple Cells

Maybe you want the number of characters in multiple cells. You can use the LEN function and add the SUM function to the formula.

For example, with the following formula you can count the number of characters in cells A2, A4, and A6:

=SUM(LEN(A2),LEN(A4),LEN(A6))

Count Characters in a Cell Range

If you want to get the total number of characters in multiple adjacent cells, you can easily list out each cell reference using the formula above, but there’s a better way. You’ll still use the LEN and SUM functions to count characters in a range of cells.

For instance, you can obtain the number of characters in cells A2 through A4 with this formula:

=SUM(LEN(A2:A4))

As you can see, you just add the cell range within the LEN part of the formula to get your total.

Tip: If you’re familiar with the SUMPRODUCT function, you can use it the same way as the SUM function in this case. For our example, you could enter =SUMPRODUCT(LEN(A2:A4)) for the same result.

Multiple Cell Ranges

To count characters in two different cell ranges, you can continue to use the SUM and LEN functions.

Here, we’ll get the count for cells A2 through A4 plus C2 through C4 with this formula:

=SUM(LEN(A2:A4),LEN(C2:C4))

In this case, you simply add both LEN formulas within the parentheses for the SUM function’s formula.

Count Specific Characters in a Cell in Excel

Maybe it’s the count of a specific character you need to find. For this, you’ll use LEN with the SUBSTITUTE function.

In this example, we want to count the number of L’s in cell A1. You would use this formula:

=LEN(A2)-LEN(SUBSTITUTE(A2,”L”,””))

You can see our result of 1 is correct. Here’s a breakdown of the formula structure from the inside out:

  • SUBSTITUTE(A2,”L”,””) replaces all instances of the letter L with an empty string in cell A2.
  • LEN(SUBSTITUTE(A2,”L”,””)) counts the characters in cell A2 (without the letter L).
  • LEN(A2) counts the total number of characters in cell A2.
  • LEN(A2)-LEN(SUBSTITUTE(A2,”L”,””)) subtracts the character count (without the letter L) from the total number of characters in cell A2.

Specific Characters Ignoring Letter Case

As you’ll notice, the above formula only gives us one instance of the letter L as the result. This is because the formula contains the uppercase letter L, so it doesn’t look for lowercase letters.

To account for this case-sensitivity, you can add either UPPER or LOWER to the SUBSTITUTE formula. In a nutshell, this then replaces all occurrences, regardless of case, with the empty text string.

Using the same example, we’ll add UPPER to the formula as follows:

=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),”L”,””))

This time, we receive the result of 3 which includes both upper and lowercase L’s.

Count Specific Characters in a Cell Range

Want to take what you’ve learned so far and go even further? In this last section we’ll show you how to count a specific character in a range of cells. You’ll basically combine LEN, SUBSTITUTE, and SUM.

With this formula, you can count the number of L’s in the cell range A2 through A4:

=SUM(LEN(A2:A4)-LEN(SUBSTITUTE(UPPER(A2:A4),”L”,””)))

Here, we keep UPPER in the SUBSTITUTE formula to find all instances of the letter L regardless of letter case.

Once you start using these functions and formulas in your worksheet, counting characters in Excel becomes easier each time. No matter what you need the character count for, let Excel do the work instead of counting characters manually.

For more, check out our tutorial for using COUNTIFS, SUMIFS, and AVERAGEIFS in Microsoft Excel.



Source : How to Count Characters in Microsoft Excel Cells

Comments

Post a Comment

Popular posts from this blog

How to Add or Remove a Secondary Axis in Microsoft Excel Charts

Image
When you have a chart with mixed data types or a dataset that varies broadly between series, consider using a secondary axis in Microsoft Excel. With it, you can display the data you need without overwhelming or confusing your viewers. When to Use a Secondary Axis As mentioned, if you have different data types or your series amounts widely vary, these are good times to use a secondary axis. For instance, you can see that the column chart below doesn’t accurately depict the visitors and sales in our dataset. There’s a large variance in the sales amounts, making it hard to get a decent picture of the number of visitors per month. Plus, the sales are in dollar amounts, whereas the visitors are basic number values . If your chart would benefit from a secondary axis like this one, here’s how to add one as well as remove a secondary axis later if you change your mind or your data. How to Add a Secondary Axis in Excel To add a secondary axis to your Excel chart, you’ll use the combo ...
Read more

How to Use and Customise the Status Bar in Microsoft Excel

Image
Excel’s status bar is one of the hidden gems (and an unsung hero) that makes it the default spreadsheet software you turn to. It quietly resides at the bottom of the Excel window, often overlooked but brimming with valuable information and customization options. In this article, we will look at it and show you how to take advantage of it. Excel Status Bar Default Options Even before you customize the Excel status bar, it’ll display some helpful information and give you access to some useful functionalities. The common Excel options you can find as default on the status bar are: Cell Mode Macro Recording Tool Accessibility Check Page Number Statistics/Mathematical Options Worksheet Views Zoom Options Let’s see what each of them does and how you can use them. Cell Mode At the leftmost side of the Excel status bar, you’ll find the cell mode indicator. It’ll inform you about the status of the currently selected cell. There are four different status modes: Ready . It ...
Read more

What Is Windows AI-Powered Copilot?

Image
Microsoft has released its brand-new artificial intelligence (AI) companion, Microsoft Copilot. Previously known as Bing Chat, Copilot has a series of new abilities that can help you get more done in less time. In this article, we’ll provide a brief description of each of Microsoft’s new AI copilots, then we’ll focus on the Windows 11 copilot and what it can do to boost your productivity. What Is Microsoft Copilot? Microsoft Copilot is an artificial intelligence chatbot you can ask to perform different tasks, including generating text and images and answering general questions. Microsoft’s plan is to have a Copilot in each of its apps to provide assistance and improve user experience across the Microsoft ecosystem. Here are the different Copilots that are currently available or slated for future release: Windows 11 Copilot: Available for free as a preview in North America, Asia, and South America, Windows 11 Copilot lets you perform tasks, get answers, and create AI-generate...
Read more