What Is Remove Leading Spaces In Excel?
The remove leading spaces in Excel technique enables us to eliminate unwanted leading spaces in a cell. We can execute the action using the inbuilt function TRIM and the Find & Replace option.
Users can remove the leading spaces in a worksheet to ensure the data they use in functions and formulas is correct and results in accurate output.
For example, the table below shows the top ten US states by land area.
The requirement is to remove leading spaces in Excel text values in column A and display the output in column B.
Then, we can apply the TRIM in Excel in the target cells to obtain the desired outcome.
In the above example, we enter TRIM() in each target cell, with the reference to the cell containing the state name in the corresponding row as the input. So, the function works as the formula to remove leading spaces in Excel cells A2:A11 values and display the output in cells B2:B11.
Thus, the outcomes in the target cells show the leading spaces deleted before the state names, irrespective of the number of leading space characters. The reason is that the TRIM() helps remove all spaces except the single space characters between two words in a cell.
Table of contents
Key Takeaways
- The methods to remove leading spaces in Excel help us delete unwanted space characters in a given data value.
- Users can remove the leading spaces in a sheet to ensure the given data values are free from unnecessary spaces, which may be challenging to detect and eliminate otherwise.
- We can utilize the TRIM Excel function and the Find & Replace option to remove unnecessary leading spaces in a cell or range.
- We can also use VBA coding to remove leading and trailing spaces in a worksheet.
How To Remove Leading Spaces In Excel Cell?
While we can utilize the TRIM()-based formula to remove leading spaces in Excel cells, the Find & Select option also helps achieve the desired outcome.
Let us see the top 4 methods in detail using examples.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
#1 – Remove Unwanted Leading Spaces In Excel Cell Using TRIM Function
The steps to remove unwanted leading spaces in Excel cell using the TRIM() are as follows:
- Choose a target cell to display the output.
- Enter =TRIM( in the target cell. [ Alternatively, type =T or =TR and double-click the TRIM function from the Excel suggestions.]
- Enter the value or cell reference in excel to the value as the argument value and close the bracket.
- Press Enter to view the specified value without the leading spaces as the function output.
Basic Example
The following table lists the top 5 best sellers in column A.
The aim is to remove leading spaces in Excel text values in column A and display the cleaned data in column B.
- Step 1: Choose cell B2 and enter TRIM().
=TRIM(A2)
- Step 2: Press Enter to execute the TRIM().
- Step 3: Using the Excel fill handle, update the function in the remaining target cells.
Let us check the cell B6 formula to check how the function works.
The TRIM() accepts the reference to the cell A6 value as the input. Next, the function removes all the unnecessary space characters in the specified text, leaving single spaces between the words.
#2 – Remove Non-Breaking Leading Spaces In Excel Cell
The steps to remove non-breaking leading spaces in an Excel cell are as follows:
- Select a target cell to display the output.
- Enter the following formula:
=TRIM(SUBSTITUTE(cell_reference,CHAR(160),””))
- Please enter the value or cell reference to the value, from which we must remove the non-breaking leading spaces, as the SUBSTITUTE()’s first argument value.
- Press Enter to view the specified cell value without the non-breaking leading spaces as the formula output in the target cell.
Basic Example
The following table shows AI terms and their definitions taken from a website.
The task is to remove the leading spaces in the texts in column B and display the cleaned data in column C.
As explained in the previous section, we can apply the TRIM() in the target cell to achieve the required output.
And for that, choose cell C2, enter TRIM(), and press Enter.
=TRIM(B2)
However, the output still shows leading spaces in the specified cell value, leading to the case of cannot remove leading spaces in Excel.
The reason is that the definitions are from the web. So, the additional spaces are non-breaking leading spaces, which the TRIM() fails to remove.
Thus, in such a scenario, we must use the TRIM() with the Excel SUBSTITUTE function and Excel CHAR function to successfully remove the non-breaking leading spaces.
- Step 1: Choose cell C2 and enter the following formula.
=TRIM(SUBSTITUTE(B2,CHAR(160),””))
- Step 2: Press Enter to view the formula output.
- Step 3: Using the fill handle, update the formula in cells B3:B5.
Let us check the cell C5 expression to understand how the formula works.
The ASCII code 160 represents a non-breaking leading space character. So, the CHAR(160) in the SUBSTITUTE() returns a non-breaking leading space character. Next, the SUBSTITUTE() accepts the reference to the cell B5 value as the first argument value.
Next, the function replaces every non-breaking leading space character with a single space character in the specified value. Finally, the TRIM() removes unwanted spaces from the text value, except for the single space character between the words.
Thus, when we cannot remove leading spaces in Excel using TRIM() as a standalone function, use the above-explained formula to achieve the desired output.
Next, we shall see methods to remove leading spaces in Excel without TRIM function.
#3 – Remove Double Spaces Using Excel FIND & REPLACE Method
The steps to remove double spaces using the Excel Find & Replace option are as follows:
- Select the source dataset in the active sheet and choose the Home tab → Find & Select down arrow → Replace option. Otherwise, press Ctrl + H.
- The Replace tab of the Find and Replace window will open. Click inside the Find what field and press the Spacebar twice to enter double spaces. Next, click inside the Replace with field and press the Spacebar once to enter a single space.
- Choose Replace All in the Find and Replace window. The single spaces will replace the double spaces. Also, Excel will show a message box stating the total replacements made in the active worksheet.
- Click OK to close the message box.
- Click Close to exit from the Find and Replace window.
Basic Example
The following table lists the top ten US tech firm CEOs.
The aim is to remove double spaces in the given dataset.
Then, here is how to remove leading spaces in Excel without TRIM function.
- Step 1: Choose the cell range A2:A11 and choose Home → Find & Select → Replace in the active worksheet.
The Replace tab of the Find and Replace window opens.
- Step 2: Enter two space characters in the Find what field by pressing the Spacebar twice.
Next, enter a single space character in the Replace with field by pressing the Spacebar once.
- Step 3: Press Replace All.
The single space character replaces all the double spaces in the chosen cell range. Also, we will see a message box stating the total number of replacements, which is 10 in the above scenario.
- Step 4: Click OK to exit the message box.
Finally, click Close in the Find and Replace window and view the required output.
#4 – Remove All Leading Spaces Using Excel FIND & REPLACE Method
The steps to remove all the leading spaces using the Excel Find & Replace option are as follows:
- Choose the source dataset in the current sheet and select the Home tab → Find & Select down arrow → Replace option. Otherwise, press Ctrl + H.
- The Replace tab of the Find and Replace window opens. Click inside the Find what field and press the Spacebar once to enter a single space character.
- Choose Replace All in the Find and Replace window. All the single spaces will be eliminated. Also, Excel will show a message box stating the total replacements made in the current sheet.
- Click OK to exit from the message box.
- Click Close to close the Find and Replace window.
Basic Example
The following table lists products and their codes.
The aim is to remove all the leading spaces in the product codes in column B.
- Step 1: Choose the cell range B2:B8 and press Ctrl + H to access the Replace tab of the Find and Replace window.
- Step 2: Enter a single space character in the Find what field by pressing the Spacebar once.
- Step 3: Click Replace All.
All the leading spaces in the chosen column B cells will be eliminated, and we will see a message box mentioning the total number of replacements. The count indicates the number of leading spaces removed in the chosen cell range.
Click OK to close the message box.
- Step 4: Choose Close to exit the Find and Replace window.
Finally, we can view the required output, as depicted below.
Important Things To Note
- The TRIM() can only remove leading spaces in Excel, which are 7-bit ASCII space characters.
- When using the Find & Replace option to remove the leading spaces in a worksheet, ensure to select the specific cell range from the source dataset.
- When we aim to remove non-breaking leading spaces in Excel, use the TRIM() with the SUBSTITUTE and CHAR functions.
Frequently Asked Questions (FAQs)
We can remove leading and trailing spaces in Excel VBA using the following steps, explained with an example.
The following image shows cell A2, containing text with leading and trailing spaces.
Here is how to remove leading and trailing spaces in cell A2 and display the output in cell B2 using VBA coding.
• Step 1: With the current sheet open, press Alt + F11 to access the VBA Editor.
• Step 2: Choose the required VBAProject. After that, select the Module option under the Insert tab in the menu.
A new module window opens.
• Step 3: Enter the VBA code in the module window to remove the leading and trailing spaces in the source data value.
• Step 4: Choose the play icon in the menu to run the code.
Finally, open the active worksheet to view the specified data without leading and trailing spaces in the target cell.
In the above example, the cell A2 value contains seven leading spaces before the first word in the text and four trailing spaces after the full stop.
So, the VBA code uses the TRIM Excel function to remove the seven leading and four trailing spaces from the specified text and display the output in cell B2.
We can remove leading spaces in Excel before numbers using the following steps:
1) Choose a target cell to show the output.
2) Type =TRIM( in the target cell. [ Alternatively, enter =T or =TR and double-click the TRIM function from the Excel suggestions.]
3) Enter the value or cell reference to the value as the argument value and close the bracket.
4) Press Enter to view the cell value without the leading spaces as the function output.
TRIM won’t remove leading space in Excel because of the following reasons:
• The characters have ASCII values different from 0 to 32 (non-printing characters).
• The characters have an ASCII value of 160 (non-breaking space characters).
Download Template
This article must be helpful to understand the Remove Leading Spaces In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Remove Leading Spaces In Excel. We learn how to remove leading spaces using Top 4 Methods with step by step examples. You can learn more from the following articles –
Leave a Reply