+3 votes
118 views
in Sell ​​on the Internet by (74k points)
reopened
Counting characters in Excel with the LONG function: step by step manual

1 Answer

+4 votes
by (490k points)
edited
 
Best answer

image

Counting characters in Excel with the LONG function: step by step manual

Everyone probably knows how to count characters and words in Word, but what is not so obvious is that Excel, the spreadsheet program , also offers this handy function, although its application is somewhat more complex. In this article we explain how to count and display the number of characters in Excel..

Index
  1. Counting characters in Excel: how and for what?
  2. LONG function: syntax
  3. How to count the number of characters in Excel
  4. LONG + SUM: count and sum characters in Excel
  5. LONG + SPACES: count and correct characters with Excel

Counting characters in Excel: how and for what?

Counting the characters entered in Excel can be very useful to get a general idea of the amount of data saved. The function in question is also very useful when combined with other functions to optimize customer data that has not been entered cleanly (for example, to remove excess spaces from the dataset). The function that is used to count characters in Excel is called LONG.

advice

If, in addition to Excel, you also want to use other Microsoft applications such as Word, Outlook or PowerPoint to manage your data effectively, IONOS Microsoft 365 packages are the perfect solution..

LONG function: syntax

It is very easy to use the LONG function, which counts the number of characters in Excel. Its syntax follows this scheme:

  =LARGO(referencia de celda)  

In the function, the cell reference in parentheses indicates the cell or range whose number of characters Excel will count.

How to count the number of characters in Excel

The LONG function is the perfect tool to count the cells in a range, the contents of those cells, or to display the total number of characters that the document contains. Here are the key steps to use it:

Step 1. If you want to count the letters in an Excel cell, enter the LONG function in an empty cell and include a reference to the cell whose content you want to analyze. To count the characters in cell B4, which contains, for example, the word? Text ?, you will have to type the LONG function in another cell (E4, for example) and reference cell B4. Excel will then automatically display the number of characters in the referenced cell, which in our example would be 5..

image
The LONG function shows how many characters the selected cell contains.

Step 2. To apply this count function to more than one cell , re-enter the function in a blank cell and reference first the first cell in the range you want to span.

image
Excel's LONG function can count characters in more than one cell.

Next, drag the fill handle (that is, the little square that appears in the lower right corner of the cell that contains the function), selecting the cells that will contain results (in this case, from E4 to E6), thus defining at the same time the range of cells that the count will cover: the LONG function will then be applied to the three corresponding argument cells (from B4 to B6) and will count the letters of each one of them:

image
The LONG function displays the number of characters in multiple cells.

LONG + SUM: count and sum characters in Excel

If you do not want to do a mere general count, but to know specifically the number of characters in certain cells and add the results, you can use the LONG function in combination with the SUM function:

 

  =SUMA(LARGO(referencia de celda);LARGO(referencia de celda);(LARGO(referencia de celda))  

The syntax in this case seems more complicated than the simple version of the LONG function, but in practice it is very easy to apply the LONG and SUM functions in combination. We show it to you with the following step-by-step manual :

Step 1. Choose a range of cells with values ​​to serve as the argument for the LONG function. In our example, we will enter the texts? Learn Excel ?,? Use Excel? and? Practice Excel patiently? in cells A3, A4, and A5, respectively.

Step 2. Then, apply the combination of the LONG and SUM functions to those cells:

  =SUMA(LARGO(A3);LARGO(A4);(LARGO(A5))  

Excel will then display the total number of characters (counted and added) in a separate cell (in this case, C7).

image
When combining the SUM AND LONG functions, the Microsoft program sums (together) all the characters contained in the selected cells.

LONG + SPACES: count and correct characters with Excel

Perhaps, however, you not only want to count characters, but also remove unnecessary spaces from your spreadsheet: to do this, you can combine the LONG function with the SPACES function . The latter is in charge of eliminating the excess spaces of an Excel text and its syntax has the following form:

  =ESPACIOS(texto)  

A typical case in which this function is very useful is when data has been entered without taking care of the layout : for example, if old customer data has been copied into a new database and unnecessary spaces have been included before or after certain elements . In such situations, the combination of the LONG and SPACES functions in Excel allows you to count the letters and at the same time eliminate the spaces. In the following example there are two spaces before the customer's name? Marcos? and two others after the name of? Carmen ?.

image
Combining the LENGTH and SPACES functions, it is possible to correct the disposition of data that has not been entered cleanly.

To combine the functions, the first step is to enter the LONG function for the first box:

  =LARGO(A3)  

Then, extend the selection, using the fill handle mentioned above, until it covers the entire desired range:

image
Excel: check number of characters with LONG function

Now when counting the letters in Excel cells as in the example, you will see wrong results are displayed: 8 instead of 6 characters in? Frames? and in? Carmen ?. These additional characters, which correspond to spaces, will be removed in the next step:

Step 1 . Create a new column and copy the original data into it, without correcting its layout. Then enter the SPACES function referring to the first cell you want to correct.

image
Excel: with the SPACES function you can eliminate excess characters

Step 2 . Now drag the fill handle to the last cell in the range. In this way, the function will be applied to all the desired data .

image
Dataset after correcting layout in Excel

Step 3. To verify that the excess spaces have actually been removed, you can double-check the number of characters the data contains by entering the LONG function in another column and referencing it to the already corrected data.

image
Using the LONG function you can check the number of characters in the range once corrected.

This final check shows that by using the LENGTH and SPACES functions in combination, all unnecessary spaces have been removed.

advice

Excel not only allows you to count characters, but also cells, thanks to the COUNTIF function.


...