Step-by-Step Tutorial on Clearing Extraneous Space Characters in MS Excel Cells

Step-by-Step Tutorial on Clearing Extraneous Space Characters in MS Excel Cells

Paul Lv12

Step-by-Step Tutorial on Clearing Extraneous Space Characters in MS Excel Cells

Copying text into Microsoft Excel often results in annoying leftover whitespace, and it’s tedious work to manually remove trailing, leading, and any other extra spaces from the cells in your spreadsheets. Luckily, Excel has two features to remove them with ease.

Related: How to Copy and Paste Only Visible Cells in Microsoft Excel

Ways to Delete Whitespace in Excel

In Excel, to remove the leading, trailing, and extra spaces between words in a particular string, use the TRIM function . This function deletes all spaces except for single spaces between words.

To remove all spaces, including the ones between words, use Excel’s Replace feature. You can quickly do this across your entire spreadsheet, or limit it to a specific area.

Related: How to Use the TRIM Function in Microsoft Excel

Remove Leading, Trailing, and Extra Whitespaces in Excel

To start removing the leading, trailing, and extra whitespaces from your cells, first, open your spreadsheet with Microsoft Excel. We will use the following spreadsheet that has extra spaces.

An Excel spreadsheet with extra spaces.

Select the cell next to the first record in your spreadsheet. This is where your text without the extra spaces will appear.

Select the cell next to the first record.

vMix 4K - Software based live production. vMix 4K includes everything in vMix HD plus 4K support, PTZ control, External/Fullscreen output, 4 Virtual Outputs, 1 Replay, 4 vMix Call, and 2 Recorders.
This bundle includes Studio 200 for vMix from Virtualsetworks, HTTP Matrix 1.0 automation scheduler, and 4 introductory training videos from the Udemy vMix Basic to Amazing course.

In the selected cell, type the following TRIM function and press Enter. In the function, replace

    `B2`

with the cell where your first record is.

=TRIM(B2)

Enter the TRIM function in the cell.

From the bottom-right corner of where you typed the TRIM function, drag down so the formula is copied for all your records.

Drag the TRIM function cell downwards.

You now have your text without any extra spaces in your spreadsheet.

Extra spaces removed from cells.

Got any duplicate rows in your spreadsheet? If so, it’s easy to remove them .

Related: How to Remove Duplicate Rows in Excel

Allavsoft Batch Download Online Videos, Music Offline to MP4, MP3, MOV, etc format

How to Remove All Spaces in Excel

To get rid of all whitespaces from your spreadsheet, use Excel’s Replace feature as follows.

First, open your spreadsheet and select the cells from which you want to remove spaces.

Select the cells containing extra spaces.

In Excel’s ribbon at the top, click the “Home” tab.

Click the "Home" tab in Excel.

In the “Home” tab, from the “Editing” section, select the “Find & Select” option.

Choose "Find & Select" in the "Home" tab.

From the expanded menu, select “Replace.”

Click "Replace" in the "Find & Select" menu.

Excel will open a “Find and Replace” window. Here, click the “Find What” box and type a space. Leave the “Replace With” field empty. Then click “Replace All” at the bottom of the window.

Enter a space in the "Find What" box and click "Replace All."

Excel will find and delete all spaces from your worksheet.

If you realize you made a mistake, quickly bring your spaces back by pressing Ctrl+Z on Windows or Command+Z on Mac.

All spaces removed from an Excel worksheet.

Project Manager - Asset Browser for 3Ds Max

And that’s how you keep your entries neat and tidy by removing any unwanted whitespaces from them!


Need to remove blank rows or columns from your Excel spreadsheets? There are easy ways to do that.

Related: How to Quickly and Easily Delete Blank Rows and Columns in Excel

  • Title: Step-by-Step Tutorial on Clearing Extraneous Space Characters in MS Excel Cells
  • Author: Paul
  • Created at : 2024-08-28 05:24:08
  • Updated at : 2024-08-29 05:24:08
  • Link: https://win-able.techidaily.com/step-by-step-tutorial-on-clearing-extraneous-space-characters-in-ms-excel-cells/
  • License: This work is licensed under CC BY-NC-SA 4.0.