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.