Getting Rid of Spaces in Cells
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.
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:
- Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.
- In the Find What box, enter two spaces.
- Make sure the Replace With box is empty.
- Select the Match Entire Cell Contents check box.
- Click on Replace All.
- Repeat steps 2 through 5, but this time use only one space in step 2.
- 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:
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell)
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:
Dim rCell As Range
Dim rText As Range
Set rText = Cells.SpecialCells( _
For Each rCell In rText
If Trim(rCell.Value) = "" Then
Set rText = Nothing
Set rCell = Nothing
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.
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!
Leave your own comment:
Comments for this tip:
shabzo 10 Nov 2015, 12:49
try this . insert a new column next to the one that has spaces ( eg row b1)
motherchook 21 Sep 2015, 01:37
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
FRUSTRATED HOURS 25 Nov 2014, 00:09
WHAT A JOKE. EXCEL DOESN'T ACCEPT A SIMPLE COMMAND LIKE TRIM. IT IS A JOKE THAT EVERYONE'S FORCED TO USE BY MICROSOFT.
Wijnand Engelkes 03 Jul 2014, 04:57
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.
HANDELL 01 Jul 2014, 11:11
@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
Wijnand Engelkes 08 Aug 2013, 05:36
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.
Michael (micky) Avidan 07 Aug 2013, 05:34
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)
Wijnand Engelkes 07 Aug 2013, 03:21
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
after step 3
and a b (SIX spaces between)
becomes after step 1
a | | | | | |b
after step 2
after step 3
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.
Donald Berg 04 Aug 2013, 21:26
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.