WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

6 Easy Ways to Remove Leading Spaces in Excel [2024]

August 1, 2022
60.2K Views

You can ensure that the data in your Excel worksheet is clutter-free, clean, and ready to use by removing spaces before texts or numbers. It also helps you avoid errors and make it easier to work with the data, especially if you need to sort or filter the data based on specific criteria improving the efficiency of working. While copying data from external sources like databases, websites, or different formats, that data is victim to having leading spaces that make data visibility and functionality dull.

To make your data visually appealing and easy to analyze, all these leading spaces need to be removed, but how to remove spaces in Excel is the first question. Follow this guide until the end to know 6 Easy Ways to Remove Leading Spaces in Excel before texts or numbers.

Method-1 How to use "TRIM Function" to remove spaces in Excel

1. Select the cell or range of cells containing the data you want to clean. The below names have spaces between the first and last names.  

excel remove leading spaces

2. In a blank cell, enter the formula =TRIM(A2), where A2 is the cell containing the first data point.

excel remove leading spaces trim

3. Press Enter to apply the formula to the cell, and then copy the formula to the remaining cells in the range. The output will have logical spacing between first and last names.

excel remove leading spaces using trim

100% Compatible with MS Office.    WPS Office: Use Word, Excel, and PPT for FREE, No Ads.


Method-2 How to use the "Find and Replace" tool to remove leading spaces in Excel

1. Select the cells you want to clean.

2. Press Ctrl + H to open the "Find and Replace" dialog box.

excel remove leading spaces find

3. In the "Find what" field, enter a single space character by pressing the spacebar once and leaving the "Replace with" field blank.

4. Click on "Options" and then check the "Match entire cell contents" box.

excel remove leading spaces replace

5. Click "Replace All" to remove all leading spaces in the selected cells.

excel remove leading spaces without formula

Method-3: How to use "Data Validation" to remove spaces before data in Excel

1. Select the cell or range of cells where you want to prevent leading spaces.

2. Click on the "Data" tab in the Excel ribbon and select Data Validation.

how to removeleading spaces in excel

3. In the "Settings" tab of the "Data Validation" dialog box, choose "Custom" from the "Allow" drop-down list.

excel remove leading spaces after numbers

4. In the "Formula" field, enter the following formula: =LEFT(A2,1)" ". The A2 is a cell reference having your first entry.

remove excel leading spaces

5. Click on the "Error Alert" tab and set the "Style" to "Stop".

6. Enter an error message to display when a user tries to enter a value with leading spaces (e.g., "Please Enter a valid value without leading spaces").

excel remove leading spaces data validation

7. Click "OK" to close the "Data Validation" dialog box. Whenever the user enters a value with a leading space, Excel will prompt this message.

how to remove spaces in excel after text

Method-4: How to use "Trim formula" to remove space before Text

1. Select the cell where you want to enter text.

2. In the formula bar, enter the following formula: =TRIM(B5)&" ".

excel remove leading spaces before character

3. Press Enter to complete the formula and drag the formula down to the last entry. You will get your desired results.

excel remove leading spaces before text trim function

Method-5: How to use "Trim formula" to remove space before the number

1. Select the cell where you want to enter a number.

2. In the formula bar, enter the following formula: =TRIM(A2)

Note: Change "A2" to the cell reference of your selected cell.

excel remove leading spaces before numbers

3. Press Enter to complete the formula and drag the formula down to the corresponding cells.

excel remove leading spaces before numbers using TRIM

Method-6: How to use "VBA" to remove space before the number

Excel VBA provides three built-in TRIM functions:

  • TRIM: This function removes any leading or trailing space characters from the text.

  • LTRIM: This function removes any leading space character from the text.

  • RTRIM: This function removes any trailing space character from the text.

Below is the VBA code that removes leading space characters from the selected cells, and this code can be used to remove any leading spaces from the text in the selected cells.

Sub RemoveLeadingSpaces()  

    Dim selectedRange As Range  

    Dim cell As Range  

      

    ' Set the selected range to the currently selected cells  

    Set selectedRange = Selection  

      

    ' Loop through each cell in the selected range  

    For Each cell In selectedRange  

        ' Remove leading spaces from the cell value  

        cell.Value = LTrim(cell.Value)  

    Next cell  

End Sub  

logo

The Functionality of Trim and The Find and Delete Method

The TRIM function in Excel is used to remove leading and trailing spaces from a text string. It is useful for cleaning up data where extra spaces may have been inadvertently included. For example,  if a cell contains the text "Data", the TRIM function will remove the leading and trailing spaces, resulting in "Example". 

On the other hand, the Find and Delete method involves manually searching for specific characters or patterns within a text string and deleting them. This method allows you to find and remove specific characters, such as spaces or other symbols, based on your criteria.

The significant difference between these methods is that TRIM is designed to remove all leading and trailing spaces, regardless of their position in the text string. It does not provide the flexibility to target specific characters. In contrast, the Find and Delete method allows you to identify and remove specific characters or patterns, providing more control over the deletion process.

WPS Office: Use Word, Excel, and PPT for FREE, No Ads.

logo

WPS Office - Free Alternative to Microsoft Office to Remove Spaces in Excel

You can use all these methods on Excel and if you are not comfortable with using Excel, you can try a free alternative called WPS Office. With WPS Office, you'll enjoy a plethora of features that are not available in Microsoft Office, it will be easier to find and remove space in excel before texts or numbers.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs about removing leading spaces in Excel

Q1. How accurate is removing leading spaces in Excel?

The TRIM function is a built-in function in Excel and is widely used by users. It is accurate and reliable in removing leading spaces before text and numbers. The macro approach I suggested is also a commonly used method and should work accurately as well.

Q2. How complex is it to remove leading spaces in Excel?

Using the TRIM function to remove leading spaces before text or numbers is a straightforward method. It only requires a basic understanding of Excel formulas and can be done in a few steps.

Q3. Would removing leading spaces cause errors?

Removing leading spaces in Excel using the TRIM function or a macro should not cause any errors if done correctly. However, there are some situations where it could potentially cause errors or unexpected results. Also, different methods may cause different errors in Excel. Therefore, it is important to check the change in Excel to make sure what you edit is correct.

Q4. Do removing leading spaces help officers improve efficiency?

Removing leading spaces in Excel can help improve efficiency by making it easier to work with the data and reducing the risk of errors. When working with large data sets or performing data analysis, removing leading spaces can help ensure that the data is consistent and accurate.

Summary

Removing leading spaces from your data makes it clear, clutter-free, easy to analyze, and aesthetic to present. It also makes critical decision-making easy because all the data is well organized without any scrap data. The above-mentioned methods are easy to implement and will enable you to remove leading spaces from your Excel data. These functions can be used in Excel as well as in WPS Office.

WPS Office is a lightweight and free alternative to MS Office that you can install on your PC for free. It has all the required office productivity suite with the inclusion of a robust PDF tool. It is compatible with MS Office and has additional features as compared to MS Office like a tabbed interface, customizable interface, cloud integration, collaboration, and PDF tool.  WPS Office is a smarter choice for maximizing your efficiency and achieving extraordinary results so go on and try it.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.