Finding the Number of Words in a Text String in Excel
In this short post, I would like to discuss how you can find the number of words in a text string.
Before we proceed I would like you to download an example excel file by clicking here. This will help you understand the concept better and you can also try the formula in the file yourself to see how it works.
Now if you open the file, in Column A, you will see the names of certain products. Some products have longer names and some have shorter names. To find the number of words in each of these product names, you can use the formula given below:
[Assuming A2 to be the cell that contains the text]
The roles of the different functions / parts in the above formula are given below:
SUBSTITUTE(A2,” “,””): The SUBSTITUTE(A2,” “,””) function replaces all the spaces in cell A2 with no spaces.
LEN(SUBSTITUTE(A2,” “,””): The LEN function calculates the number of characters in the text string except the spaces.
LEN(TRIM(A2)): The LEN function calculates the number of characters in the text string with the spaces. TRIM function will remove any extra spaces before / after the first / last word in the text string and between words. This is important because any extra spaces will result in the length calculated by LEN function to be incorrect. [You can see one such case in the text in Cell A15 in the example file]
IF and ISBLANK: If we don’t use the IF and ISBLANK function, the formula will return 1 even when the cell is empty. To guard against this it is important to use these functions in the formula. [For example, Cell A32 in the example file is blank]
Significance of +1:
LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””) will compute the difference between the number of characters with spaces and without spaces. In other words, this will calculate the number of spaces in the text string. Now, if you think a little you will realize that the the number of words in a sentence is nothing but the number of spaces plus 1. And so, this formula contains +1 to add 1 to the number of spaces to give the result as the number of words in the text string.
Now, you can simply pull the formula down to know the number of words in all the text strings in the column.
I really hope you found this article interesting. If you did, I encourage you to follow us on social media to get updates on all my new posts and get all the extra content I share on those platforms. The links are given below.
Have a great day! Happy Excelling!
Get my ULTIMATE EXCEL SHORTCUTS COURSE
worth $40 for FREE!!
Subscribe to our newsletter and get interesting stuff and updates to your email inbox.
Thank you for subscribing.
Something went wrong.