How to count the number of characters in an Excel cell. The number of characters in an Excel cell – a function and a counting formula

Users who are engaged in various calculations in Excel often encounter situations when they need to count the number of characters in a particular cell, several lines, or an entire column. You can do this manually, however, in order to get the most accurate result in the shortest possible time, it is recommended to use Excel functions.

Counting characters from one cell

In order to accurately count the number of characters in one cell, you must use the “DLSTR” function. Procedure:

1. Initially, you need to select the cell in which the function will be added with the mouse. After that, you need to click on the symbol for adding the “fx” function.

2. The “Function Wizard” window will appear in front of the user. From the category that is selected by default, you need to select the line “Text”.
3. A list with functions will open, from which you need to select “LENGTH”. To complete the operation, click on the “OK” button.
4. The next step is to set the function argument in the window that appears. Here it is necessary to write the coordinates of the cell, the characters from which you want to count.

Important! In the function argument setting line, you can write the address of only one cell. If you specify a range or coordinates of several scattered cells, the function will not work.

5. When the value of the argument is set, you need to click on the “OK” button.

If all actions are performed correctly, the number of characters from the cell whose coordinates were specified in the function argument will be displayed in the cell where the function was originally installed.

Counting characters from a row or column

If you need to count the number of characters from an entire row or column of a table, then you do not need to enter separate counting functions for each cell. You can save time and automate this process. The process of counting the number of characters from one column:

1. First of all, you need to select the adjacent cell opposite the top cell of the column.
2. Perform all actions to count the number of characters from one cell, as described in the previous method.
3. Move the mouse cursor to the cell with the result. Wait until a black cross appears in its lower right corner.

4. Hold LMB, stretch it to the very last cell of the column.

After that, in all cells adjacent to the column, the results of calculating the number of characters from individual lines will appear.

Now you need to calculate the sum of the obtained values. No need to use a calculator, just follow a few simple steps:

1. Initially, it is necessary to select all cells with the results of individual calculations.
2. Next, go to the “Home” tab on the main panel.
3. Find the “Sum” button on the right side of all instruments. It’s in the Editing group.
4. Immediately after pressing this button, the sum of all individual values ​​will appear under the selected cells.

Counting characters from scattered cells

One of the most difficult computational problems is to count the number of characters in cells that are scattered throughout the table and are not adjacent. If you do it manually, it will take a lot of time, and there is also a high chance of making a mistake. However, even such a complex process can be automated. Procedure:

1. Initially, you need to select the cell in which the formula will be added. Put the “=” sign, write the “DLSTR” function, write the coordinates of the cell from which you need to get the number of characters in brackets, put the “+” sign after the bracket, repeat the procedure with the function and coordinates for another cell. And so for all the cells that should participate in the total count.

2. After preparing the entire list, it is necessary to double-check the entered data in order to avoid mistakes. If everything is correct, press the “Enter” button.

In the initially designated cell, the sum of the number of characters from all marked cells will appear.

Count certain characters from one cell

Another possible situation when working with Excel tables is that you need to count not the total number of characters, but only individual characters, numbers, letters. There is no separate function for performing such an operation in Excel. However, you can use a homemade formula that, when written correctly, shows accurate results with any characters. To create a formula, you need to combine two functions:

• DLSTR;
• SUBSTITUTE.

The general form of the finished formula is the following expression: uXNUMXd DLSTR (cell coordinates ) – DLSTR (SUBSTITUTE ( cell coordinates ; desired character ; “”)).

The essence of the work of this formula will be easier to understand by a visual example. For example, the line to be counted is in column “B”, you need to count how many digits “1” are in this line. The expression is carried out according to the following algorithm:

1. The formula initially calculates the total length of the required string. The LONG(B2) function is activated.
2. The next action is the SUBSTITUTE function. It is needed in order to find and remove all the numbers “1” from this line. In this case, the unit place is replaced by a space.
3. The next step is to calculate the length of the entire string without taking into account the number “1”. In this case, the action will look like this: DLSTR(SUBSTITUTE(B2; “1”; “”)).

As a result, in the previously marked cell, the user will receive the number of deleted characters from string B2.

Conclusion

It is easy to calculate the number of characters manually, if the cells are filled with a small number of characters, then for the general calculation it is necessary to obtain the sum of all characters from several cells. However, if we are talking about counting characters from tens or hundreds of cells, manual calculations will take a lot of time, and the result is likely to contain errors. The use of individual Excel functions and their combination with each other allow you to automate most of the calculation processes and greatly simplify them.