How to count char­ac­ters in Excel using the LEN function: step-by-step in­struc­tions

The LEN function is the perfect tool for per­form­ing this cal­cu­la­tion on specific cells and their contents or for de­term­in­ing the total number of char­ac­ters in your data set. Here are the major steps involved in this process.

Step 1: If you want Excel to count the char­ac­ters in a cell, enter the LEN function in an empty cell alongside the cell reference for the cell you want to analyse. For example, if cell B4 contains the word “TEXT”, insert the LEN function with the cell reference (i.e. B4) in another cell (here E4). Excel will then auto­mat­ic­ally calculate the total number of char­ac­ters in the cell, which is “4” in our example.

Step 2: If you want to apply this character counting function to multiple cells, you must first enter the function again in an empty cell and then enter the cell reference for the first cell in the cell range to be analysed.

Then, using the cell fill box (the small rectangle sur­round­ing the cell with the function), select the return value cells (here E4 to E6) for the entire cell range con­tain­ing the char­ac­ters to be counted. The LEN function will then be applied to all three example cells (B4 to B6) and count the number of char­ac­ters for these Excel cells sim­ul­tan­eously.

Excel will then display the number of char­ac­ters - which have been counted and added up - in a separate cell (here C7).

A typical scenario where you should use this com­bin­a­tion of functions is when dealing with messy data that has been trans­ferred. For example, if you were to transfer old customer data to a new database and discover that there were leading or trailing spaces in the customer data, you could use a com­bin­a­tion of the Excel LEN and TRIM functions to count the number of char­ac­ters and remove any extra spaces at the same time. In the following example, you will see two spaces before the customer “Irina” and two after the customer “Kon­stantin”.

You can apply this function to the entire data set as shown above using the fill box.

If you count the Excel char­ac­ters in the example, you will see that the return values are incorrect for “Irina” (7 instead of 5) and “Kon­stantin” (12 instead of 10). These extra spaces need to be removed which can be done using the following steps.

Step 1: Create an ad­di­tion­al column and copy the original incorrect data into it. Then, apply the TRIM function to the first row involved.

Step 2: Now, drag the fill box to the bottom cell in the data set and apply the function to all the data selected.]

Step 3: To verify that the extra spaces have actually been removed, you can check the new corrected text data using the LEN function in an adjacent column.