How to count the number of characters in Excel cell and range - LEN function with examples of using to calculate text length

How to count the number of characters in Excel cell and range - LEN function with examples of using to calculate text length

In this article, let's see how to count the number of characters in a cell using the LEN function and combine it with other functions to count the number of characters in a column, range, and defined characters, unspecified characters, lowercase characters, or characters after a certain character.

LEN function

Excel has a standard LEN function for counting the number of characters.

Syntax

=LEN(text)

Function arguments

Text - text, the number of characters in which you want to calculate. Required argument. All characters, including spaces, are taken into the result of the counting.

Counting the total number of characters

Using the above function, it is easy to count the number of characters in one cell. Enter the following formula in the cell:

=LEN(A1)

Replace cell A1 with any other cell. Excel will count all the characters in the cell and return the result as an integer. It is important to note that the function counts ALL characters, including spaces, punctuation and line breaks.

In a column or range

In addition to counting characters in a one cell, sometimes we need to do it in a range or a column. There are a few ways to accomplish this.

The simplest, but also the least flexible way, is to sum several LEN functions in a formula. To do this, enter = in the formula bar and add the few LEN functions to the selected cells using the + character or SUM function.

=LEN(A1)+LEN(A2)+LEN(A3)+LEN(A4)
=SUM(LEN(A1);LEN(A2);LEN(A3);LEN(A4))

But if there are many cells, the formula will be too large and complex, so it is more practical to use the SUMPRODUCT function.

=SUMPRODUCT(LEN(A1:A3))

The SUMPRODUCT function takes arrays as arguments, then multiplies the values within each array and returns the sum of the results.  The array that the LEN(A1:A3) function will return can be represented as follows:

{
	"A1": {
		"123"
	},
	"A2": {
		"123"
	},
	"A3": {
		"123"
	}
}

In the array, each element has only 1 value showing the number of characters in the cell, so the function simply summarizes all these values.

But this method also has disadvantages, because it only summarizes the full range, without breaks. To summarize a few ranges, you can use it in combination with the first method, summarizing a few ranges, for example:

=SUMPRODUCT(LEN(A1:A7))+SUMPRODUCT(LEN(A12:A15))+LEN(B2)

Or with the SUM function:

=SUM(SUMPRODUCT(LEN(A1:A7));SUMPRODUCT(LEN(A12:A15));LEN(B2))

Counting without some characters

For example, we want to count how many characters are in a cell without considering spaces and punctuation marks. To do this, we can use a formula with the LEN and SUBSTITUTE functions. For example, if we don't want to take into account the "!" character, use this formula:

=LEN(SUBSTITUTE(A1;"!";""))

In this formula, first the SUBSTITUTE function replaces all "!" characters with "" (empty values), and then the LEN function counts the characters in the resulting string.

This is a simple replacement of a single character. If you need to replace more than one character, you should use the nested SUBSTITUTE function.

=LEN(SUBSTITUTE(SUBSTITUTE(A1;"!";"");",";""))

As you can see, it replaces the "!" and "," characters.

Counting specific characters

To count the number of specific characters in a cell, you need to count the total number of characters and minus the number without defined characters from it. We can use the formula from the previous example. To calculate how many digits "5" are contained in a cell we can use this formula:

=LEN(D21)-LEN( SUBSTITUTE(D21;"5";""))

Counting without HTML tags

I recently found an interesting question, and I want to post the solution here. The question is to count the number of characters without HTML tags. I decided to write a custom function.

Public Function DeleteHTMLTags(tocell As Range) As Integer
    On Error GoTo IfError

    celltext = tocell.Value

    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(<([^>]+)>)"
    regex.Global = True
    regex.IgnoreCase = True
    regex.MultiLine = True

    newtext = regex.Replace(celltext, "")
    lentext = Len(newtext)
    DeleteHTMLTags = lentext
    Exit Function

    IfError:
        DeleteHTMLTags = "Error"
End Function

To create a custom function, open the VB editor window. To do this, click the "Visual Basic" icon on the "Developer" tab or press the Alt+F11 key combination.

Add a new module to the book.

Paste the code into the module and save. Now you can close the editor window and use the new function =DeleteHTMLTags(A1) in this book.

Please rate this article
(4.3 stars / 9 votes)