How to Count Space Before Text in Excel (4 Ways)

This article mainly focuses on counting spaces before a text in an Excel cell. Extra spaces can be caused by directly copying and pasting from a source or for many different reasons. Whatever the case might be, you may want to count the spaces before, after, or in between texts. Of course, you can count each space manually. But we will be focusing on formulas and VBA to count space before the text in Excel here in this article.


Download Practice Workbook

You can download the workbook used for this example with the dataset from the box below.


4 Ways to Count Space Before Text in Excel

In this tutorial, I am going to show you four different ways you can count space before the text in Excel. For demonstration, I will be using the following dataset.

As you can see from the picture, there are different amounts of space before the starting of text in each cell, we will count the numbers of these.


1. Combining TRIM Function with LEFT and FIND Functions

The first method we focus on here is a formula, which is a combination of TRIM, LEFT, and FIND functions.

The TRIM function takes a text argument and removes all the extra spacings between the characters. The LEFT function takes several characters out of a string from the start of a function. FIND function takes two primary arguments- the character/set of characters it will find, and the text or string which it will find from.

Here is how you can use them for this purpose.

Steps:

  • First, select cell C5.
  • Write down the following formula.
=FIND(LEFT(TRIM(B5),1),B5)-1

how to count space before text in excel using find, left and trim functions

  • Now, press Enter.

  • Then, select the previous cell and click and drag the Fill Handle Icon down to the end of the list to fill up the rest of the dataset.

counting space before text in excel

πŸ”Ž Breakdown of the Formula:

πŸ‘‰ TRIM(B5) takes the text from cell B5, removes extra spaces, and gives the output Excel.

πŸ‘‰ LEFT(TRIM(B5),1) takes Excel as the argument and takes out the first character from the text, E.

πŸ‘‰ FIND(LEFT(TRIM(B5),1), B5) function finds the first instance of E in the text, which is at the first position.

πŸ‘‰ FIND(LEFT(TRIM(B5),1), B5)-1 finally returns the amount of space before it, which, in this case, is zero. But for other texts, there are values.


2. Count Space Before Text Using SEARCH and TRIM Functions

Although the formula used in the above example is the best one to count space before the text, there are others you can also use. In this section, we will go through the formula, combined using the SEARCH and TRIM functions.

The SEARCH function, similar to the FIND function takes two arguments- the characters it is searching for and the text it is searching from and returns the position of the first occurrence of the first argument in the text. The TRIM function takes a text argument and normalizes the spaces between the characters in it.

To use the formula follow these steps.

Steps:

  • First, select cell C5.
  • Then write down the following formula in the cell.
=SEARCH(TRIM(B5),B5)-1

how to count space before text in excel using search and trim functions

  • Press Enter on your keyboard and you will have the result for the first cell.

  • Now, select the previous cell and click and drag the Fill Handle Icon down to the end to find out the number of spaces for the rest of the cells.

dragging auto fill icon down

πŸ”Ž Breakdown of the Formula:

πŸ‘‰ TRIM(B5) takes value from cell B5 as input and removes all the unnecessary spaces between texts, and in this case returns Excel.

πŸ‘‰ SEARCH(TRIM(B5), B5) takes Excel and cell B5 as input arguments and searches the first character of the first argument in the second argument. Here, it is returning the position of E in cell B5, which is 1.

πŸ‘‰ As SEARCH(TRIM(B5), B5) returns the first position where the text starts SEARCH(TRIM(B5), B5)-1 finally returns the number of spaces before it in the text. In this context, it is zero, but there are other values for different cells.

Read More: How to Add Space Between Text in Excel Cell (4 Easy Ways)


Similar Readings


3. Applying LEN and SUBSTITUTE Functions

Aside from the formula used above, you can also use a combination of the LEN and SUBSTITUTE functions.

The LEN function returns the length of a text. The SUBSTITUTE function takes three arguments- a text to replace from, characters it will replace and the new set of characters it will use instead. The function finally returns the string after replacing characters.

Steps:

  • First, select cell C5.
  • Then write down the following formula in the cell.
=LEN(B5)-LEN(SUBSTITUTE(B5," ",""))

how to count space before text in excel using len and substitute functions

  • Now press Enter on your keyboard. You will have the number of spaces in cell B5.

  • Select the previous cell again and drag down the Fill Handle Icon to fill up the rest of the cells with the formula for their respective cells. It will give you the number of spaces for those texts.

dragging down len and substitute function

πŸ”Ž Breakdown of the Formula:

πŸ‘‰ SUBSTITUTE(B5,” β€œ,””) removes all the spaces in cell B5.

πŸ‘‰ LEN(SUBSTITUTE(B5,” β€œ,””)) returns the length of cell B5 without any spaces.

πŸ‘‰ LEN(B5) returns the length of cell B5.

πŸ‘‰ LEN(B5)-LEN(SUBSTITUTE(B5,” β€œ,””)) finally returns the difference between the total length of the text and the length of text without space.

Read More: How to Find and Replace Space in Excel (5 Methods)


4. Embedding VBA Code to Count Space Before Text

You can use Visual Basic for Applications(VBA) to create custom functions to count space before the text in Excel. To do this, you need the Developer tab on your ribbon. If you don’t have it, you have to enable the developer tab.

Once you have the tab shown, you can follow the steps to find out the number of spaces before text.

Steps:

  • Select the Developers tab from the ribbon, then select Visual Basic from the CodeΒ group.

  • In the Visual Basic window. Click on Insert, then select Module from the drop-down list.

inserting module in vba to count space before text in excel

  • Then, in the new module, write down the following code.
Function NumSpacesStart(str As Variant) As Integer

Dim trimmed As String

trimmed = LTrim(str)

NumSpacesStart = InStr(1, str, Left(trimmed, 1), vbTextCompare) - 1

End Function
  • Now, close the VBA window and select your cell.
  • Then write down the following formula in the cell.
=NumSpacesStart(B5)

entering formula created with vba

  • Press Enter. You will have the number of spaces there is in cell B5.

  • Select the previous cell. Click and drag the Fill Handle Icon to fill up the formula for the rest of the cells. You will have the number of spaces in each cell before the text.

filling up vba formula with auto fill


Conclusion

That concludes all the methods you can use to count space before the text in Excel. Hope you find this helpful and easy to understand. If you have any queries or suggestions, feel free to let us know below. For more guides like this, visit ExcelDemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

1 Comment
  1. Hello, Thank you for making this post on how to create a function to count the number of words in an excel cell.

Leave a reply

ExcelDemy
Logo