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:
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:
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: 
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.
Note:
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When developing formulas, you may need to reference the very last value in a particular column. This can seem perplexing, ...
Discover MoreNeed to enter a check mark into a cell? There are a number of ways you can get the desired character, depending on the ...
Discover MoreOne day you are just editing your worksheet like you normally do, then you see an error that says "Cannot shift object ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Comments