How to Count the Number of Spaces of Text String in Excel?

Powerful spreadsheet software like Microsoft Excel provides a variety of features for manipulating and analysing data. Counting the number of spaces in a text string is a frequent task that comes up while working with text data. Excel has a number of formulae and approaches to assist you successfully complete this work, whether you need to count the words in a cell or locate the presence of particular characters.

In this tutorial, we'll walk you through utilising various Excel techniques to count the spaces in a text string. This tutorial will give you step-by-step directions, useful hints, and practical examples to assure your success in precisely counting spaces, regardless of your level of Excel proficiency.

Count the Number of Spaces of Text String

Here we will get the first value using the formula, then use the autofill handle to complete the task. So let us see a simple process to know how you can count the number of spaces in a text string in Excel.

Step 1

Consider an Excel sheet where you have a list of strings with spaces between the words, similar to the below image.

First, to count the spaces between the words in the first cell, click on an empty cell, in this case cell B2, and enter the formula as =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) and click enter.

Empty cell > Formula > Enter.

Step 2

Then, to get all the other values, drag down using the autofill handle from the first value. The final result will be similar to the image below.

This is how you can count the number of spaces in text strings in an Excel cell.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can count the number of spaces in a text string in Excel to highlight a particular set of data.

Pradeep Kumar

e

Updated on: 21-Aug-2023

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started