Excel Formula to Count Words in a Cell

IntermediateText Manipulation

I need to know how many words are in a specific sentence in cell A1.

The Formula

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

How It Works

1

First, we calculate the total length of the text.

2

Then we remove all spaces and calculate the length again.

3

The difference tells us how many spaces there are. Adding 1 gives the word count.

💡 Pro Tips

  • • Test the formula with sample data before applying to your entire dataset
  • • Use absolute references ($A$1) when copying formulas to prevent reference errors
  • • Consider error handling with IFERROR() for more robust formulas
  • • Document your formulas with comments for future reference

Related Formulas

Excel Formula to Extract Email Address from Text

I have a cell containing text and an email address (e.g., 'Contact john@example.com for info'). I want to extract just the email.

Intermediate

How to Remove Extra Spaces in Excel

My data has weird spacing issues (spaces at the start or end). How do I clean it up?

Beginner

How to Capitalize the First Letter of Each Word

My list of names is all lowercase (e.g., 'john doe'). I want to make them 'John Doe'.

Beginner

Need a Custom Formula?

Can't find exactly what you need? Our AI can generate custom Excel formulas based on your specific requirements.

Try AI Generator

Formula Details

Difficulty:Intermediate
Category:Text Manipulation
Steps:3