3 quick ways to remove extra spaces between words or delete all spaces from Excel cells. You can use trim formula, Excel Find & replace or special Excel add-in to clean up cells' content.
When you paste data from an external source to an Excel spreadsheet (plain text reports, numbers from web pages, etc.), you are likely to get extra spaces along with important data. There can be leading and trailing spaces, several blanks between words and thousand separators for numbers.
Consequently, your table looks disorderly and becomes difficult to use. It may be a challenge to find a customer in the Name column since you search for "John Doe" which has no excess spaces between the names while the way it looks in your table is "John Doe". Or numbers can't be summed up, and again extra blanks are the ones to blame.
In this article you'll find how to clean up your data.
Trim blanks between words to 1, remove trailing / leading spaces
For example, you have a table with 2 columns. In the column Name, the first cell contains "John Doe" written correctly without excess spaces. All other cells have extra blanks between the first and the last names. At the same time these cells have irrelevant blanks before and after the full names known as leading and trailing spaces. The second column is called Length and shows the number of symbols in each name:
Excel has the Trim formula to use for deleting extra spaces from text. Below you can find the steps showing how to use this option: Now pick the first cell in the original column and press Shift + F10 or the menu button . Then just press V.
That's it! We deleted all excess blanks with the help of the formula trim(). Unfortunately, it's a bit time-consuming, especially if your spreadsheet is rather big.
Note. If after using the formula you still see extra spaces (the last cell on the screenshot), please have a look at If the TRIM function doesn't work. This option needs fewer steps, but allows only deleting excess spaces between words. Leading and trailing spaces will also be trimmed to 1, but will not be removed. If you often import data to Excel from external sources and spend much time polishing up your tables, the Trim Spaces tool will clean data imported from the web or any other external source. It removes leading and trailing spaces, excess blanks between words, non-breaking spaces, and line breaks. To remove all extra spaces in your worksheet, including excess paces between words, this is what you need to do: Isn't it faster than with the two previous tips? If you always deal with data processing, this tool will save you hours of precious time.Use the Trim formula to remove extra spaces
=TRIM(A2)
Using Find & Replace to remove extra spaces between words
3 clicks to neat data with Trim Spaces tool
Remove all spaces between numbers
Suppose, you have a workbook with numbers where the digits (thousands, millions, billions) are separated with spaces. Thus Excel sees numbers as text and no math operation can be performed.
The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option:
- Press Ctrl + Space to select all cells in a column.
- Press Ctrl + H to open the "Find & Replace" dialog box.
- Press Space bar in the Find What field and make sure the "Replace with" field is empty.
- Click on the "Replace all" button, and then press Ok. Voila! All spaces are removed.
Using formula to remove all spaces
You may need to delete all blanks, like in a formula chain. To do this, you can create a helper column and enter the formula: =SUBSTITUTE(A1," ","")
Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.
Then follow the steps from the part using formula to remove extra spaces between words to 1
Video: how to remove spaces in Excel
260 comments
Thank you buddy you saved the day :)
Your tool worked fast and deleted leading and trailing spaces before numbers in a 4500-row spreadsheet! Thanks!
Thank you.
In my excel i don't have Ablebits Data option please suggest how to get it.
Hello, Girish,
Please note that the Ablebits tab will appear in your Excel Ribbon once you install our software. If you want to use our Trim Spaces add-in to remove extra spaces from your worksheet, feel free to install a fully functional trial version of Ablebits Ultimate Suite for Excel and see if the tools work for you.
thank you!
Thanks buddy
Dear All,
Kindly help me into provide a formula for the below case.
I need to delete space after number.
581086
581086
581086
Dear Ahamed,
Thank you for your question.
Please note that you can remove extra spaces using the Trim formula. However, our Trim Spaces add-in can help you solve this task faster and easier. It is available as a part of our Text Toolkit that contains 8 useful add-ins to manage text data in Excel (remove extra spaces, substrings and non-printable characters, change case, split cells, etc.).
I took the time to actually say thank you this time, usually i don't bother.
remove space between numbers did not work for me!
Worked for Me.
53 X 101 after replace space i got only 53X 101 . But i want 53X101... Please help
I want revmove space 53X 101
good one
Thank you
I have a problem in my excel
like 1zw76589246565992 . in right hand side one space is there
if I try to trim that one it appears trim formula. I don't want it because I want to duplicate the files like 1zw76589246565992 .- 1zw76589246565992
So please tell me how can I remove the space without appearing formula?...
I have a problem in my excel
like 1zw76589246565992 . in right hand side one space is there
if I try to trim that one it appears trim formula. I don't want it because I want to duplicate the files like 1zw76589246565992 .- 1zw76589246565992
So please tell me how can I remove the space without appearing formula?...
Thanks, I searched a lot for removing space but it worked with call cleaner add-in Finally.
Perfect... its really wonderful
Hi, How am I going to copy paste formula with spaces(filtered) between cells?Thank you!
More then one space in front of numbers , how to remove?
Excellent
1000
5000
2000
how can i remove the space, plz help
very nice its works........
but can you tell me now how to copy and paste this corrected excel worksheet data in another sheet. because its not pasted in another sheet.
and even we delete the previous and unstructured data then automatically corrected data invisible or unstructured in shape of #REF! this.
so please help me how to use this corrected data or paste this data in another sheet.
thanks
Reds 19 Joey Votto Red Cool Base Stitched MLB Jersey.Great service of online buy usa soccer sweatshirt,and enjoy
our cheap nfl authentic jerseys china 79% off.
Its very healpfull
Very helpful. This saved so much time.
Very helpful.
Thanks a lot
Thanks a lot. Very useful.
VERY HELPFULL
Great job , it really worked for me
Thanks
dear Sir,
i would like to remove some extra words from one column,
how to use trim formula here
How to remove space before and after of /
Please help on it.
Select the whole column/width Choose find option/Ctrl "S" select space button and replace it .
thanks very much for a good work done
Precisely I got what I want
can I make my number look like $22 instead of $ 22
Depending on the version of Excel you are running, if you format the cell as currency, it should automatically remove any unnecessary spaces for you.
I hope this helps
Thank you very much Renat Tlebaldziyeu (Ablebits.com Team)
It worked.......
04 100 950105 - this is the original
04100950105 - i want to make it like this
Hi kamlesh,
You should use the following formula:
=SUBSTITUTE(A1, " ", "")
i hve tried but it is not cming....can u pls guide in formula
i want to remove space between 04 100 950105 it has to be 04100950105
Try Joel's solution above:
"Didnt work for me too, however found the right way. Select and highlight the space you want to get rid of. Copy this space and select the find and replace. Paste this space in find and replace with nothing. I guess it wasnt a space in the first place. Whatever it was you now find it by copying whatever it was. Hope it works for you."
Thank you. I just used this to solve a major problem at work now. God bless.
tnx a lot
thanks a lot for your suggestion .
this was wonderful. it saved me hundreds of working minutes
Thanks a lot
Thank you mannn...
thank you, very helpful
Thanking you. very useful
plz find ans. this problem
you cannot use this command on protected seet to use this command you must first unprotect the seet
Hello tushar,
Please see
How to unlock cells in Excel (unprotect a sheet)
Perfect save the day!
Its amazing,,
Very helpful to everyone
how to remove blank cells from the column in excel sheet.
I try to copy number for web to paste into an excel but i still can't get rid of that space could someone help me please here is the subject.
(2 917.20 )
Helo,
Please guide me how to remove space before a Number like below
" 0.29"
" 1.459"