How to count characters and words in Excel
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."
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, 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:
=𝙻𝙴𝙽(𝚃𝚁𝙸𝙼(𝙰𝟸:𝙰𝟷𝟾))
-
𝙻𝙴𝙽(𝚂𝚄𝙱𝚂𝚃𝙸𝚃𝚄𝚃𝙴(𝙰𝟸:𝙰𝟷𝟾," ",""))
+𝟷
#exceleration #excel #globalexcelsummit
---
The Global Excel Summit is the world's largest gathering of Microsoft Excel users and experts.
Find us on:
🌐 Website: https://globalexcelsummit.com
👔 LinkedIn: / global-excel-summit
🆇 X: https://x.com/ExcelSummit
🤳 TikTok: / globalexcelsummit
📸 Instagram: / globalexcelsummit
🟦 Facebook: / globalexcelsummit
🧵 Threads: https://www.threads.net/@globalexcels...