Andrew Moss
Technical Writer
February 16, 2024
February 16, 2024

Count the characters and words in text strings

Counting the number of characters or words in a string is useful for various reasons. You might be conducting text-based analysis, language processing tasks, or planning social media posts.

In the example, a table houses a list of hotel reviews in A2:A18. How do we find the character and word count of each one?

Character Count

The LEN function returns the number of characters in a text string, so it's a straightforward task of referencing the reviews range inside.

=𝙻𝙴𝙽(π™°πŸΈ:π™°πŸ·πŸΎ)

Word Count

The formula required is best understood broken down. Let's use the review in A2 for the example: "The room was clean and comfortable."

1.

In E2, use TRIM to remove any excess spaces from the string (there aren't any in this case).

=πšƒπšπ™Έπ™Ό(π™°πŸΈ)
The room was clean and comfortable.

2️.

In F2, use SUBSTITUTE to swap each space for an empty string, therefore removing all spaces.

=πš‚πš„π™±πš‚πšƒπ™Έπšƒπš„πšƒπ™΄(π™°πŸΈ," ","")
Theroomwascleanandcomfortable.

3️.

In G2, subtract the character length of the string 𝘸π˜ͺ𝘡𝘩𝘰𝘢𝘡 spaces from the string 𝘸π˜ͺ𝘡𝘩 spaces.

=𝙻𝙴𝙽(π™΄πŸΈ)-𝙻𝙴𝙽(π™΅πŸΈ)
35 – 30 = 5

4️.

Add 1 to include the word after the final space.

=𝙻𝙴𝙽(π™΄πŸΈ)-𝙻𝙴𝙽(π™΅πŸΈ)+𝟷
6

Put together, this is the final formula for retrieving the word count of each review:

=𝙻𝙴𝙽(πšƒπšπ™Έπ™Ό(π™°πŸΈ:π™°πŸ·πŸΎ))
-
𝙻𝙴𝙽(πš‚πš„π™±πš‚πšƒπ™Έπšƒπš„πšƒπ™΄(π™°πŸΈ:π™°πŸ·πŸΎ," ",""))
+𝟷

For more Excel tips and tricks like this, check out our Video Tutorials page.

Join the Master Club

Your exclusive all-access pass to our entire digital learning experience for a whole year.