How to Count Specific Characters in a Column in Excel (2 Cases)

In order to track recurrence, it might be required to count specific characters in column in excel. Suppose you have a customer who repeats purchases, you might want to count how many times that specific customer makes that purchase. In this article, we will learn how to get that customer data just by using a formula or two.

Key Takeaways

➤ In the cell where you want the count, write this formula:
=SUM(LEN(A:A)-LEN(SUBSTITUTE(A:A,”Emily Johnson”,””)))/LEN(“Emily Johnson”)
➤ Replace A:A with the column, and “Emily Johnson” with your characters.
➤ Press Enter

That formula might need a little explanation so that you can understand it clearly. If you learn the functions properly, you can customize the formula later for other uses. Therefore, keep reading the article to learn more about the functions in the formula.

Download Practice Workbook

Counting Specific Character Set in a Column

In this dataset, we have some customer satisfaction data. From the customer names, we want to find out how many times a specific customer has ordered.

In order to do that, we are going to follow the steps below:

➤ In the F2 cell, where we want the count of the characters, we will write this formula:

=SUM(LEN(A:A)-LEN(SUBSTITUTE(A:A,”Emily Johnson”,””)))/LEN(“Emily Johnson”)

Explanation

The LEN function at the beginning counts all of the characters in column A. Then, the SUBSTITUTE function replaces Emily Johnson with no characters, so that we can count the characters again with LEN, and then subtract it from all the characters in the column. At the end, the length of the characters is divided so that we don’t count the characters in the customer’s name. The SUM function is used to sum all the cells, as without it, we cannot work with a column.

➤ Press Enter.

Counting Specific Characters with Character Total

In the previous method, we counted the instances of specific characters. But what if you want to count them, including the number of characters you looked up in the column? This is how to do that:

➤ Using F2 as a helper cell, write this in the formula bar:

=SUM(LEN(A:A)-LEN(SUBSTITUTE(A:A,”Emily Johnson”,””)))

Explanation

You are right, this is almost the same formula as the previous method. The difference is that we didn’t divide the result by the length of the input string. As a result, we get the character count, including the number of characters we asked it to search for.

Frequently Asked Questions

How to count specific words in Excel column formula?

If you need to count specific cell data, use this formula:
=COUNTIF(A:A,”Emily Johnson”)
Replace A:A with your column, and Emily Johnson with your word. This function only returns the number of cells that contain your word, so if one cell has the same word multiple times, it won’t be counted. Also, unless you use wildcards, if your cell has more characters than the ones you are searching for, it won’t be counted.

How do you count the characters in a column?

This formula can be helpful:
=LEN(A:A)
Replace A:A with your column.

How to count unique values in sheet?

Use this formula:
=COUNTUNIQUE(A:A)
This formula counts unique values in the A column.

What is the formula for count specific characters?

For counting specific characters in one cell, use this formula:
=LEN(A2)-LEN(SUBSTITUTE(A2,”E”,””))
Replace A2 with the cell where you want to look for the character, and E with the character.

How many characters are in one cell in Excel?

Excel supports up to 32767 characters in one cell.

Wrapping Up

In this article, we have shown you two methods to count specific characters in a column in excel. We hope that you will be able to apply the methods you learned from this article in your work. Download the practice file to test the methods at your convenience. Leave a comment with your suggestions below.

Post Views: 15