• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Count the Number of Words in a Cell

Count the Number of Words in a Cell

Working recently at a large Internet company they needed to find how many words were in a cell. This was because they had imported hundreds of thousands of keywords that customers had used to find their site through search engines.

To analyse this data they wanted to count how many words were in each cell containing keyword searches. Excel provides many text functions for managing and manipulating the text in the cells of your spreadsheet. The following formula did the job.

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

This formula subtracts a version of the cell without spaces between each word from a version still with spaces. This finds out how many spaces there are. There will be one less space than there are words so the resulting answer has a 1 added to it to find how many words there are.

The LEN function is used to find how many text characters are in the cell.
The SUBSTITUTE function is used to replace the spaces between each word with nothing making one long word.

The TRIM function is used to remove any spaces at the beginning or end of the cell content. This cleanses the cell of any erroneous spaces put in by user error, or error on import.

Count the number of words in a cell

Related Posts:

  • Excel Formula to Find the Cell Address of a Value
    Excel Formula to Find the Cell Address of a Value

Reader Interactions

Comments

  1. P.G.Mathew Tharakan says

    26 December 2012 at 9:03 am

    I have some problem arise to use this formula
    Winter holiday your answer is 2, I got 1
    Formula is
    =len(trim(A4))-Len(substitute(trim(A4),” “,” “))+1
    please help
    Mathew

    Reply
    • computergaga_blog says

      27 December 2012 at 1:16 pm

      You do not need a space in the third argument of the LEN function. The formula has been revised below;
      =len(trim(A4))-Len(substitute(trim(A4),” “,”“))+1

      Reply
  2. Roc says

    13 September 2016 at 8:28 am

    Hi!
    Is this the same formula for Google Sheets? I’m trying to applicate it in, but it doesn’t work.
    Do you any alternative?

    Reply
    • computergaga says

      16 September 2016 at 6:41 am

      Hi Roc,
      Just tested it in Google Sheets and it worked fine. Double check the formula you are using. Are the cell references correct?
      Alan

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·