Sanjida Mehrun Guria
Counting text or characters is a very common phenomenon among regular excel users. Especially when we work with a large dataset with various criteria. In Excel, we can use the COUNTIF function to count text from the left. Unfortunately, it is not possible to use the COUNTIIF and LEFT functions together to count a specific text at the start of a cell. But we can do the same with the COUNT, IF, and LEFT functions. In this article, we will show the procedure with easy steps. Apart from these, we can also apply some other functions to count text from the left.
Download Practice Workbook
Get this sample file and practice by yourself.
Step-by-Step Procedures of Counting Text at Start with COUNT, IF & LEFT Functions
To describe the whole process, we will show the steps one after another for better understanding. So, without further delay, let’s follow the steps below.
STEP 1: Create Dataset for Specific Text
- First, create a dataset in the Cell range B4:C15 with the information of Keywords and their Search Volumes of different types of coffee makers.
STEP 2: Insert the Text You Need to Count
- Then, insert the Search text B in Cell F6 as we want to count how many cells have this letter at the beginning of a word in the dataset.
STEP 3: Apply Formula
- After that, insert this formula in Cell F7.
=COUNT(IF(LEFT(B5:B15,1)="B",1,""))
- Lastly, press Enter to see the result.
Final Output
- In the end, we have found the number of occurrences of the specific text at the beginning of a cell.
Read More: How to Use COUNTIF Function to Count Text from List in Excel
Similar Readings
- Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)
- How to Use IF and COUNTIF Functions Together in Excel
- Excel COUNTIF Function with Conditional Formatting (7 Examples)
- How to Apply COUNTIF Between Two Cell Values in Excel
Alternative Solutions for Counting Specific Text in Excel
You can also count characters or texts with the following solutions as well. For illustration, we will use the same dataset as above.
1. Combine SUMPRODUCT, LEFT & LEN Functions to Count Particular Text from Left
We can combine the SUMPRODUCT, LEFT, and LEN functions to count the number of the particular text.
- To count text, first, apply this formula in Cell C18.
=SUMPRODUCT(--(LEFT(B5:B15,LEN(C17))=C17))
- Afterward, press Enter.
- Finally, you will get a similar output as counted text.
Read More: Excel COUNTIF to Count Cell That Contains Text from Another Cell
2. Apply COUNTIF Function to Return Number of Specific Text
Another solution is that we can use the COUNTIF function to count the number of specific texts. For this, go through this process.
- First, insert this formula in Cell F7.
=COUNTIF(B5:B15,"*B*")
- Then, hit Enter.
- That’s it, we get the accurate output as before for the counted number of the text.
- Even, if you need to find any specific word other than character, you can apply this formula.
=COUNTIF(B5:B15,"*Best*")
- Apart from this, you can also find multiple words with this formula.
=COUNTIF(B5:B15,"*Best Espresso*")
Things to Remember
- Any of the methods above does not require formatting the output cell as Number. Because all the formulas return the value as a number.
- If you are using an older version of Excel, then you have to press Ctrl + Shift + Enter to get the output as it is an array formula.
Conclusion
Henceforth, I hope it was a helpful article for you on how to count text or characters with COUNT, IF, and LEFT functions in excel. Also, we tried to explain two alternative solutions to count text. Let us know if you have any other methods to suggest for counting specific characters. Follow ExcelDemy for more Excel-related tutorials.
