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?
The LEN function returns the number of characters in a text string, so it's a straightforward task of referencing the reviews range inside.
=π»π΄π½(π°πΈ:π°π·πΎ)
The formula required is best understood broken down. Let's use the review in A2 for the example: "The room was clean and comfortable."
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.
In F2, use SUBSTITUTE to swap each space for an empty string, therefore removing all spaces.=πππ±πππΈππππ΄(π°πΈ," ","")
Theroomwascleanandcomfortable.
In G2, subtract the character length of the string πΈπͺπ΅π©π°πΆπ΅ spaces from the string πΈπͺπ΅π© spaces.
=π»π΄π½(π΄πΈ)-π»π΄π½(π΅πΈ)
35 β 30 = 5
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.
Conversation
As you comment, let's keep the discussion friendly and respectful to add value to the conversation.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.