
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.
