Count Words in an Excel cell

Last Updated on 29/04/2024
Reading time: 3 minutes

This article will show you 2 techniques to count the words in an Excel cell.

Method with TEXSPLIT

TEXTSPLIT is a new function for Excel 365. Count the words in a cell with this function is very easy

As the name suggests, TEXTSPLIT splits your text according to a delimiter. For instance, in this example, TEXTSPLIT will write the text in as many cells as necessary with this formula

=TEXTSPLIT(B2," ")

TEXTSPLIT splits your text by delimiter

And when you write this function inside the COUNTA function, you find the number of words in your cell.

=COUNTA(TEXTSPLIT(B3," "))

Count Word with TEXTSPLIT

Technique with more formulas

Step 1: Count all characters (including the spaces)

The starting point for counting the number of words is to count all the characters in the cell. For this, the LEN function is used. But that's not all. We also need to ensure no "parasitic" spaces at the beginning or the end of the string. And for that, there is the TRIM function.

=LEN(TRIM(B2))

Count the Number of letters

Step 2: Remove all spaces

The trick is to remove all the spaces and count the number of letters again 😉

We are therefore going to remove all the spaces present using the SUBSTITUTE function. The character to replace is the space " " and we replace it with nothing, i.e. "" (2 quotes placed side by side)

=SUBSTITUTE(B2," ","")

Remove all the spaces of the string

Step 3: Count the number of remaining characters

This time we will count the number of characters remaining after removing the spaces.

=LEN(D2)

New number of letters

Step 4: Count the number of words in Excel

All that remains is to make the difference between the 2 containing cells without forgetting to add 1 unit.

=C2-E2+1

Number of words in the cells

Integrate these steps into a LAMBDA function

For Excel 365 or Excel Online users, you can integrate all the steps into a single formula and call it by a custom name. To do that, you must use the LAMBDA function.

With this technique, you secure your result because you are sure that your end-users won't make a mistake in the writing of one of the steps. Writing the LAMBDA function is quite complex. The 4 steps are integrated into the LET function; it is the only way to proceed.

=LAMBDA(text,LET(TrimSpace,TRIM(text),LengthText,LEN(TrimSpace),DelSpace,SUBSTITUTE(TrimSpace," ",""),LengthText-LEN(DelSpace)+1))

This formula should be in a named range from the menu Formulas > Define a name.

Menu to create a custom named range

Then, you give a name for this formula; like COUNTWORDS, and you paste the previous formula into the Reference TextBox

Create your custom formula

And then, you just have to write this custom formula in a cell with the text as an argument.

Custom formula to count words

1 Comment

  1. Rick Rothstein
    13/05/2022 @ 23:34

    In your Lambda, you should replace the number 1 at the end of it with this so that the function returns the number 0 for an empty cell instead of returning the number 1...

    (LEN(Text) > 0)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Count Words in an Excel cell

Reading time: 3 minutes
Last Updated on 29/04/2024

This article will show you 2 techniques to count the words in an Excel cell.

Method with TEXSPLIT

TEXTSPLIT is a new function for Excel 365. Count the words in a cell with this function is very easy

As the name suggests, TEXTSPLIT splits your text according to a delimiter. For instance, in this example, TEXTSPLIT will write the text in as many cells as necessary with this formula

=TEXTSPLIT(B2," ")

TEXTSPLIT splits your text by delimiter

And when you write this function inside the COUNTA function, you find the number of words in your cell.

=COUNTA(TEXTSPLIT(B3," "))

Count Word with TEXTSPLIT

Technique with more formulas

Step 1: Count all characters (including the spaces)

The starting point for counting the number of words is to count all the characters in the cell. For this, the LEN function is used. But that's not all. We also need to ensure no "parasitic" spaces at the beginning or the end of the string. And for that, there is the TRIM function.

=LEN(TRIM(B2))

Count the Number of letters

Step 2: Remove all spaces

The trick is to remove all the spaces and count the number of letters again 😉

We are therefore going to remove all the spaces present using the SUBSTITUTE function. The character to replace is the space " " and we replace it with nothing, i.e. "" (2 quotes placed side by side)

=SUBSTITUTE(B2," ","")

Remove all the spaces of the string

Step 3: Count the number of remaining characters

This time we will count the number of characters remaining after removing the spaces.

=LEN(D2)

New number of letters

Step 4: Count the number of words in Excel

All that remains is to make the difference between the 2 containing cells without forgetting to add 1 unit.

=C2-E2+1

Number of words in the cells

Integrate these steps into a LAMBDA function

For Excel 365 or Excel Online users, you can integrate all the steps into a single formula and call it by a custom name. To do that, you must use the LAMBDA function.

With this technique, you secure your result because you are sure that your end-users won't make a mistake in the writing of one of the steps. Writing the LAMBDA function is quite complex. The 4 steps are integrated into the LET function; it is the only way to proceed.

=LAMBDA(text,LET(TrimSpace,TRIM(text),LengthText,LEN(TrimSpace),DelSpace,SUBSTITUTE(TrimSpace," ",""),LengthText-LEN(DelSpace)+1))

This formula should be in a named range from the menu Formulas > Define a name.

Menu to create a custom named range

Then, you give a name for this formula; like COUNTWORDS, and you paste the previous formula into the Reference TextBox

Create your custom formula

And then, you just have to write this custom formula in a cell with the text as an argument.

Custom formula to count words

1 Comment

  1. Rick Rothstein
    13/05/2022 @ 23:34

    In your Lambda, you should replace the number 1 at the end of it with this so that the function returns the number 0 for an empty cell instead of returning the number 1...

    (LEN(Text) > 0)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *