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 EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide for FREE!

Thank you for subscribing.

Something went wrong.

HUGE LIMITED TIME DISCOUNT ON THE BEST ADVANCED EXCEL COURSE ENROLL NOW!!
excel_look_professional
Making Excel Worksheets Look Professional – A Case Study
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
FREEZE PANES
Freeze Panes in Excel – The Complete Guide
HOW TO LEARN EXCEL
How to Learn Excel and Be a Spreadsheet Champion
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
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
Pivot Tables
How To Convert Pivot Table Into Frequency Distribution
How to Convert Pivot Table into a Frequency Distribution in Excel

GET MY EXCEL GUIDE FOR FREE
COMPLETE EXCEL FUNCTIONS AND SHORTCUTS GUIDE

Subscribe to our newsletter and get my Complete Excel Functions and Shortcuts Guide 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!"