How To remove leading spaces from text in Excel

Method 1: Using the TRIM Function

The TRIM function removes all extra spaces (including leading and trailing spaces) from text.

1. Enter Data:

   • Suppose your data is in Column A (e.g., A1:A10).

2. Use the TRIM Function:

   • In a new column (e.g., Column B), type the following formula:

     =TRIM(A1)
     

   • Press Enter. This removes leading (and extra) spaces for the text in A1.

3. Copy the Formula Down:

   • Drag the fill handle (small square in the bottom-right corner of the selected cell) down to apply the formula to other rows.

4. Replace Original Data (Optional):

   • Copy the cleaned data from Column B.
   • Right-click on Column A → Select Paste Special → Choose Values.

5. Delete the Helper Column:

   • Once done, you can delete Column B.

Method 2: Using Text-to-Columns

If spaces only appear at the beginning (leading spaces), this method works well.

1. Select Your Data:

   • Highlight the column containing the data with spaces.

2. Go to Text-to-Columns:

   • On the Data tab, click Text to Columns.

3. Select Delimited:

   • Choose the Delimited option → Click Next.

4. Remove All Delimiters:

   • Ensure no delimiter options (like Tab, Comma) are selected → Click Next.

5. Set Column Data Format:

   • Select General as the column data format → Click Finish.

This removes leading spaces from the selected cells.

Method 3: Find and Replace

1. Select Your Data:

   • Highlight the column or range of cells.

2. Open Find and Replace:

   • Press Ctrl + H to open the Find and Replace window.

3. Set Up Find and Replace:

   • Find what: Type a single space ( ).
   • Replace with: Leave it blank.
   • Click Replace All.