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

We will be using the following dataset. As you can see from the picture, there are different numbers of spaces before the text in each cell, and we will count their number.

Method 1 – Combining the TRIM Function with LEFT and FIND Functions

Steps:

Select cell C5.
Insert the following formula.

=FIND(LEFT(TRIM(B5),1),B5)-1

Press Enter.

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.

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 returns the amount of space before it, which, in this case, is zero. But for other texts, there are values.

Method 2 – Count Spaces Before Text Using the SEARCH and TRIM Functions

Steps:

Select cell C5.
Insert the following formula in the cell.

=SEARCH(TRIM(B5),B5)-1

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

Click and drag the Fill Handle Icon down to the end to find out the number of spaces for the rest of the cells.

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.

Method 3 – Applying LEN and SUBSTITUTE Functions

Steps:

Select cell C5.
Insert the following formula in the cell.

=LEN(B5)-LEN(SUBSTITUTE(B5," ",""))

Press Enter on your keyboard. You will get the number of spaces in cell B5.

Drag down the Fill Handle Icon to fill up the rest of the cells with the formula for their respective cells.

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,” “,””)) returns the difference between the total length of the text and the length of text without space.

This formula won’t work if there are other spaces apart from the starting ones.

Method 4 – Embedding VBA Code to Count Spaces Before Text

To do this, you need the Developer tab on your ribbon. If you don’t have it, you have to enable the Developer tab.

Steps:

Select the Developer tab from the ribbon, then select Visual Basic from the Code group.

Click on Insert, then select Module from the drop-down list.

In the new module, insert 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

Save the code.
Close the VBA window and select your cell.
Insert the following formula in the cell.

=NumSpacesStart(B5)

Press Enter. You will get the number of spaces in cell B5.

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

Download the Practice Workbook

Count Space Before Text.xlsm

<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!