Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Getting Rid of Spaces in Cells.

Getting Rid of Spaces in Cells

by Allen Wyatt
(last updated November 28, 2018)


Carole imports information into Excel from a different program, and this often leaves extra spaces in some cells. The spaces are the only things in the cells, so they appear to be empty but really aren't. Carole wondered about the best way to get rid of these unnecessary spaces.

There are a couple of approaches you can use. The first is to use the Find and Replace capabilities of Excel. Follow these steps:

  1. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
  2. In the Find What box, enter two spaces.
  3. Make sure the Replace With box is empty.
  4. Select the Match Entire Cell Contents check box.
  5. Click on Replace All.
  6. Repeat steps 2 through 5, but this time use only one space in step 2.
  7. Close the Find and Replace dialog box.

Another option is to use the Trim worksheet function. This approach is handy if the cells you want to modify are all in a particular area of the worksheet, such as a single column. For instance, if you want to get rid of the spaces from the cells in column D, you could use the following formula:


The Trim function returns the contents of cell D1 without any leading or trailing spaces. You could then copy the results of this formula and use Paste Special to paste the values back into whatever cells you desire.

Of course, if you have lots of worksheets you need to process, or if you routinely get workbooks that contain the extra spaces in cells, a better way would be to create a macro that could get rid of the spaces. Perhaps the fastest way would be to examine all the cells in the worksheet and get rid of any extra spaces:

Sub CleanSheet1()
    For Each cell In ActiveSheet.UsedRange
        cell.Value = Trim(cell)
    Next cell
End Sub

The macro steps through each cell and uses the Trim function to get rid of any leading or trailing spaces. This works on all the cells, but it may produce undesired results, depending on the characteristics of your data. If you have cells that have leading spaces—and you want those spaces—then you'll need to use a different macro. This version will give more satisfactory results:

Sub CleanSheet2()
    Dim rCell As Range
    Dim rText As Range

    Set rText = Cells.SpecialCells( _
      xlCellTypeConstants, _
    For Each rCell In rText
        If Trim(rCell.Value) = "" Then
        End If
    Set rText = Nothing
    Set rCell = Nothing
End Sub

It only checks those cells containing constants (which includes all text in the worksheet) and then checks to see if using the Trim function would result in an empty cell. If so, then the cell is cleared. If the Trim function wouldn't result in an empty cell, then no change is made to the cell.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2883) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Getting Rid of Spaces in Cells.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Adding Page Numbers

Ever want to add page numbers to your document? Word allows you to control many aspects of page numbering. Here's how to ...

Discover More

Editing a Scenario

Once a scenario is defined and saved, you can later revisit the values you created for the scenario and modify them. ...

Discover More

Pay Attention to Case when Searching for ASCII Codes

Word allows you to search for specific ASCII codes in a document. If you use codes to search for alphabetic characters, ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Forcing Editing to Be Done in a Cell

Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only ...

Discover More

Editing Individual Cells

Need to edit the data within a cell? There are any number of ways you can perform the edit; this tip documents them all.

Discover More

Turning Off Insert Options

When you insert rows, columns, or cells in a worksheet, does the resulting Insert Options icon bother you? Here's how to ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 5 - 3?

2015-11-10 12:49:31


try this . insert a new column next to the one that has spaces ( eg row b1)

2015-09-21 01:37:36


This is what worked for me to get rid of leading spaces created by html (which has used the CHAR(160) to make a space.


Edit .... Replace your selected data,
in Find What hold ALT and type 0160 using the numeric keypad
Leave Replace With as blank and select Replace All

2014-11-25 00:09:50



2014-07-03 04:57:30

Wijnand Engelkes

Thank you Handell for your comment, but I have to perform this several times a day. Your method takes much more replaces then mine. If there are 513 spaces in a string (not uncommon here) it will take 10 find/replace actions, replacing two spaces for one, reducing to 257, 129, 65, 33, 17, 9, 5, 3, 2 and finally one space.
With the triple replacement trick I described it will always take only 3 replaces. Even if you have a million spaces in a string.

2014-07-01 11:11:14


@Wijnand Engelkes why not just replace space space with space & run just keep clicking find replace again until there are no more results.

i do the standard for text lists:
find replace: tab with space
find replace: space space with space
find replace: return space with return
find replace: space return with return
find replace: return return with return

each time i repeat until there are 0 results

2013-08-08 05:36:25

Wijnand Engelkes

Maybe we misunderstand eachother.
I have a lot of files, from the mainfraime, that I have to convert to Excel. They are tab-separated but include a lot of useless spaces, somtimes at the beginning, sometimes at the end or in the middle. Sometimes I even have to combine the info of two fields (with a tab in the middle), the first one ending with 27 spaces and the next one beginning with 11 spaces. There are also fields with multiple spaces in the middle of the field.

So my main interest is to convert multiple spaces, whereever they occur (beginning, middle or end of string) to a single space. The triple replacement trick works fine.
I know of the different TRIM functions, but they do not work on my files, as did the original solution from Sharon Park. LTRIM and RTRIM are handy, but no middle spaces removing. So I use LRTIM and RTRIM and after that I use the trick.

Thank you anyway for taking your time to comment.
Wijnand Engelkes

2013-08-07 05:34:43

Michael (micky) Avidan

@Wijnand Engelkes.
The above tip has 3 sections.
I could not determine to which you refered.

1) Using the Worksheet TRIM function DOES NOT GLUE anything within the string.
It deletes extra spaces and leaves a single(!) space between the words and it also deletes all Leading/trailing space.
(Take a look at the following link).

2) The VBA TRIM function also DOES NOT GLUE the words, however it has a "big" Disadvantage.
It DOES NOT delete extra spaces within(!) the string.
(Have a look at the VBA Help as for: LTRIM, RTRIM, TRIM).

Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2014)

2013-08-07 03:21:29

Wijnand Engelkes

This does not help if the spaces are within the string. If you execute this tip thewordswillbegluedtogether because the spaces within the string are removed too. The problem is how to remove extra spaces and leave the single spaces.

You can do that by repeating replace double space by *nothing* a lot of times, but:

I always use the incredible three step replace trick:
1. replace space by space pipe (|)
2. replace pipe space by *nothing*
3. replace space pipe by space

Step 2 performs the miracle: only the second, third, etc. space are removed.

a b (FIVE spaces between)
becomes after step 1
a | | | | |b
after step 2
a |b
after step 3
a b

and a b (SIX spaces between)
becomes after step 1
a | | | | | |b
after step 2
a |b
after step 3
a b

pipe is just an example for a symbol that is not likely to occur in your sheet. You could use other symbols or even strings that are unlikely to occur.

2013-08-04 21:26:17

Donald Berg

I found a free program by a company(it was free and supported by software the company sold that was not free). It was called "trim spaces". I lost the details when I upgraded, but it was from I did purchase some of their for sale software and it was a time saver as well. Trim spaces would do a whole worksheet with one or 2 clicks, no code to write, pretty quick to do it's work, never failed me, and offered you a mini-report of spaces timed. Good stuff.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.