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.

**Table of Contents**hide

## How to Count Space Before Text in Excel: 4 Ways

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 spacing 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`

- 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.

**🔎 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.

**Read More: **How to Count Alphabet in Excel Sheet

### 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`

- 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.

**🔎 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 Count Characters in Cell Including Spaces in Excel

### 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," ",""))`

- 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.

**🔎 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 Count Characters in Cell without Spaces in Excel

### 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.

- 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)`

- 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.

**Read More: **Excel VBA: Count Characters in Cell

**Download Practice Workbook**

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

## 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.

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