How to Find the Number of Words in a Text String in Excel

Published on March 15, 2017
image_pdfimage_print

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:

=IF(ISBLANK(A2),0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1

[Assuming A2 to be the cell that contains the text]

number_of_words

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.

Facebook

Twitter

YouTube

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.

Huge Discount on the Best ADVANCED EXCEL Course. GET IT NOW!! CLICK HERE!!
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
BECOME AN EXCEL NINJA COMPLETE REVIEW
Become An Excel Ninja – Course Review
BEST EXCEL COURSES
10 Best Online Courses to Make You an Excel Wizard
HOW TO GET MY $40 COURSE FOR FREE
Get My $40 Excel Course For Free!! COUPON CODE Inside!!
14 Excel tips
14 Excel Tips – Make Yourself Super-Efficient in Excel
DATA CLEANING EXCEL
Ultimate Guide to Data Cleaning in Excel – 11 Super-Powerful Data Cleaning Techniques
RANDOMIZE LIST RANDBETWEEN
Randomize a List in Excel Using RANDBETWEEN Function
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
BEST BOOKS COURSES EXCEL VBA
Best Books and Courses to Learn Excel VBA
How to Sort Excel Worksheet in Alphabetical Order
How to Sort Excel Worksheets in Alphabetical Order
VBAPOST1
How to Add And Name A New Sheet At The Same Time Using Excel VBA
ROUNDING NUMBERS IN EXCEL
Rounding Numbers in Excel – The Complete Guide
number_of_words
How to Find the Number of Words in a Text String in Excel
HOW TO FIND IF A TEXT STRING CONTAINS SPECIFIC TEXT IN EXCEL
How to Find if a Text String Contains Specific Text in Excel
How To Indentify ULP case in a text string
How To Identify If A Text String Is In Upper, Lower Or Proper Case
How To Change Shape of Comment Box in Excel
How To Change The Shape Of Comment Box In Excel
Excel Dumbest Challenge
Excel Dumb Challenge – Do You Have Time To Waste?

Get my ULTIMATE EXCEL SHORTCUTS COURSE
worth $40 for FREE!!

Thank you for subscribing.

Something went wrong.

Share
Tweet
+1
Pin
Share
Stumble
Enjoyed this video?
number_of_words
"No Thanks. Please Close This Box!"