Count Characters and Search in Excel [An Easy Introduction]

Count characters and search in

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.

names
Names List
  • 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.
LEN
  • Close brackets and press ENTER.
len 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.

len drag

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!
sum len
  • 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.
sum 1

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.

names 1
  • 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.
leo

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.
within
  • Close brackets and press ENTER to search the text.
search done

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.

sierra
  • 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.
cell locked
  • Close the bracket and press ENTER.
second search

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.

search drag

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.

names 2
  • Type =FIND( and type the text or character you want to find in double-quotes with proper cases for the first argument.
find 2
  • 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.
find done

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