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 August 3, 2013)

9

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:

=Trim(D1)

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, _
      xlTextValues)
    For Each rCell In rText
        If Trim(rCell.Value) = "" Then
            rCell.ClearContents
        End If
    Next
    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.

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. ...

MORE FROM ALLEN

Forcing Custom Toolbars to Stay in Position

Word 2002 and Word 2003 use dynamic toolbars that can adjust themselves based on usage patterns of the tools. This can cause ...

Discover More

Creating a Quick Letter

Word provides a handy wizard that is helpful in creating basic letters quickly and easily. This tip discusses the Letter ...

Discover More

Problems Pasting Large Pictures

If you insert a large picture in your document and your text jumps all around and the picture seems to disappear, don't ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Switching Editing Location

Excel allows you to edit the contents of a cell in two places—the cell itself or in the Formula bar. If you want to ...

Discover More

Errors when Copying References to External Cells

If you copy a cell that contains a reference to external data, do you get an error? It could be due to the complexity of the ...

Discover More

Creating New Windows

If you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your data. ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 9 - 2?

2015-11-10 12:49:31

shabzo

try this . insert a new column next to the one that has spaces ( eg row b1)
=TRIM(SUBSTITUTE(B1,CHAR(160),CHAR(32)))


2015-09-21 01:37:36

motherchook

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.

SIMPLY FOLLOW THIS:-

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

FRUSTRATED HOURS

WHAT A JOKE. EXCEL DOESN'T ACCEPT A SIMPLE COMMAND LIKE TRIM. IT IS A JOKE THAT EVERYONE'S FORCED TO USE BY MICROSOFT.


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

HANDELL

@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).
http://tinypic.com/r/s59503/5

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)
ISRAEL


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.

Example:
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 AbleBits.com. 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
Subscribe

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.