This article is a detailed guide to counting characters and searching values in Excel. We will learn to count characters in a cell and search for a word in a cell that results in the character position of that word all with the help of formulas.
Also read: How to Flip Data, Change Case and Combine Files in Excel
Steps to count characters in Excel
Let’s get started with this quick and easy guide to counting characters in a cell in Microsoft Excel.
We have a set of names listed below. Let us count how many characters do they contain using the LEN formula. The LEN formula counts numbers, letters, characters, and spaces in a cell.
- In a blank cell, type the LEN formula. The syntax of the LEN formula looks like this.
LEN(Text)
- Type =LEN( and select the cell with the text you want to count characters for.
- Close brackets and press ENTER.
You can see that the LEN formula has counted the characters in the cell. The text “Sierra Leone” contains 12 characters without the quotes that are- 8 for Sierra, 1 space, and 5 for Leone.
To get count the characters for other names in the list, drag down the cell from the lower right corner to autofill the formula for all names.
Summing the total characters counted in Excel
To sum the characters counted in multiple cells at once, use the SUM function with the LEN formula. Here’s how you can do it.
- Type the SUM and the LEN function in a blank cell.
- Type =SUM(LEN( and select all cells with LEN formula applied to them.
- Close the bracket twice because you have used two formulas in a single syntax. Do not press ENTER right away!
- Press CTRL+SHIFT+Enter on your keyboard to apply the formula.
- Or simply use the SUM formula alone to sum the total count of characters.
You can see that we have summed the number of characters counted in multiple cells.
Steps to search text or characters in Excel
We will now learn to search a text or characters from a cell in Excel using the SEARCH formula.
The SEARCH formula gives the result as a character number that denotes the starting position of the text or character from left to right. The SEARCH formula is not case-sensitive.
Let’s get started to use the SEARCH function in Excel. Let us search text from the same name list again.
- Type the SEARCH function in a blank cell. The syntax of the SEARCH function looks like this.
SEARCH(find_text,within_text,[start_num])
- Type =SEARCH( and type the text or character you want to search in double-quotes for the first argument.
We are finding the position of the text “Leo” from the adjacent cell.
- Put a comma and select the cell address from which you’re searching the text in the second argument.
- Close brackets and press ENTER to search the text.
You can see that we get the result as 8 which denotes that the text “Leo” starts at the 8th character position in that cell.
This method will only help you find the position of the first available position of that text character. It will not display the position if the same character or text has been repeated in that cell.
To find out the next position of the same character, follow these steps.
After you have found the first position of the character in a cell, use the reuse the formula using the start_num argument this time.
We are finding the letter “e” in a cell this time.
- In a new blank cell, repeat the same steps as mentioned above until you reach the third argument i.e. start_num.
- On the start-num argument, select the cell in which you found the first position of the character before that is, 3. Now, add +1.
- Now, lock the cell address of the text (the one in the within_text argument). Select the cell address and press F4 or Fn+F4 on your keyboard.
- Close the bracket and press ENTER.
You can see that the cell shows 9 which denotes that the next “e” is on the 9th character position in the cell.
Now that our cell address is locked, we can simply drag the cell towards the right to get the positions of the remaining “e” in the text.
After dragging, the third cell now displays 12 which means the next “e” is on the 12th position.
Using the FIND function to find text in Excel
The FIND function is also similar to the SEARCH function in Excel. The only difference between the two is that the FIND function is case-sensitive. Any text or character that you specify between double-quotes must be in proper cases.
Recommended Read: How to Find and Replace in Excel Like a Pro
Let’s use the FIND function and learn how it works.
- Type =FIND( and type the text or character you want to find in double-quotes with proper cases for the first argument.
- Put a comma and select the cell address from which you’re finding the text in the second argument.
- Close brackets and press ENTER to apply the formula.
You can see that the FIND function has found the position of the text in the cell.
Conclusion
This was all about counting characters and finding and searching text or characters in Microsoft Excel. If you have any doubts about counting or searching characters in Excel, drop a comment below.
Reference: ExcelJet