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.
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.
Related Articles
- How to Count Specific Characters in a Cell in Excel
- How to Count Specific Characters in a Column in Excel
- How to Count Occurrences of Character in String in Excel
<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello, Thank you for making this post on how to create a function to count the number of words in an excel cell.