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
265 comments
FT-20-03-015557
FT-20-03-015568
FT-20-03-016249
FT-19-12-005014
FT-20-03-016227
FT-19-12-004990
FT-20-03-016236
I choosed the formula of "Replace all", but the result is showing that, excel can't do it in protected data, why ?
Hello Naseef!
I recommend reading this article on data protection in Excel on our blog.
I had a strange situation with excel spaces. There was a range of cells with numbers in them. In all the cells there were several spaces before the numbers. Some cells had 27 paces before the number and others had more. I tried trim and it did not work. I also tried find and replace, but it did not work as find and replace can replace only a single space at a time, but here we had multiple spaces. So I copied the range of cells into a blank word document and did the find and replace to find all spaces and replace with with blanks. All the spaces were removed and I copied back the range of cells to excel, without any spaces of course. Hope this helps.
@ Charles
I had same issue with various number of spaces in each cell just after the core numbers data. The Word tip works well. Many thanks for sharing this.
I have same issue, i have already using TRIM, SUBSTITUTE, CHAR(160), CHAR(32), copy to document/notepad and did the find and replace to find all spaces and replace with with blanks but all not working. Something I missed?.
2 232 239.38
922 766.08
79 331.90
297 858.47
166 524.86
445 863.54
212 086.95
107 807.58
50 269.83
377 522.56
219 221.27
158 301.29
1 212 093.09
920 945.35
113 798.98
Hello!
Please have a look at this article How to remove spaces in Excel - leading, trailing, non-breaking
Notice also the CLEAR function.
I hope it’ll be helpful.
This worked! thanks for sharing
Thank You Very Much
Removing spaces using find and replace option and substitute function can put up all the strings together.
How do I remove the line separator in a bulk mobile leads? Example is below
206-200-0000
206-200-0001
2062000000
2062000001
Hello Jimmie!
Please use the following:
=SUBSTITUTE(A1, "-", "")
If Space is in the start
Like eg. Number in the cell start with space then what " 123456789"
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get?
Select the columns and press ctrl+H
Find=(-)
Replace (keep Black) then Replace All
Thank you so much - the trim formula has saved me so much time :)
all of my entries are like "4.83 (19.12)" and i want to delete the portion in percentages because i want to find the minimum value of the first number for a large number of cells. does anyone know how to do this? I have tried above methods with no luck
How to remove space after all the digits, as all above method tried but purpose not fulfilled.
lovely..... May Allah accept this as a deed and give us guidance to the righteous path.
EXCELLENT
Yes
May Allah praise us
Thanks for sharing the skills. It is really helpful
THANKS FOR THE SOLUTION
FIND & REPLACE IS BEST WAY
Thanks a lot. It helped me tons. Cheers!!!
A very handy way to remove spaces. Thanks so much, and keep it up.
"
111013491504"
"
111013491515"
"
123016013840"
"
111013491504"
"
1029232644"
"
1029207094"
"
1029183115"
"
1029212005"
"
1029211950"
how can remove this space
select column
Press Ctrl H to open the "Find & Replace"
Press " 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.
Select column
press F, dialog box opens, then click on special, then click on blank and click on OK
NOW LEFT CLICK ON BLANK CELL and select delete
select Shift cell up and press OK.
This will save me a huge problem from my boss. Huge data supplied came with spaces between phone numbers and we needed to contact them via SMS. Thanks in advance
This article saved me alot of time and frustration.
1. select the whole column or the cell you want to convert to numbers
2. Right click and then press "Format Cells"
3. click on "Numbers" and adjust the number of decimals you want in the 'Decimal Places'
How to remove spaces in excel
Ex: i have an excel sheet which is in 10 thousand phone numbers in a sheet but the sheet having more spaces.spaces means not a front space and back space,only space from up or above space
like phone number above space and number like this 9999999999,how to remove above space
Space
9999999999----> phone number
Please tell me the formula
Find and replace then press CTRL+J on replace then click the Replace All button.
=SUBSTITUTE(C13," ","")
I can still see trailing spaces on my spreadsheets after removing them via Find and replace method. Please help
I had the same issue. Here is what u can do, select (highlight) the "space" after the data and copy (ctrl+c). Then in find and replace, paste (ctrl+v) the "space" that u have copied into the "Find What" and leave the "Replace With" empty.
Good Luck!
IG:Mokhschild
Brilliant, just fixed my problems too! Seems the "spaces" weren't spaces
Microsoft Docs contributor guide overview - Contributor Guide | Microsoft Docs
I have looked for hours and hours a solution to replace a "space" that was not a space. And your comment Mokhschild is really brilliant.
I had an imported document where the original numbers had the format "1 000". After the import, these numbers had been automatically converted to text : "1 000". But impossible to remove the "space" in the middle. Which means imossible to convert them back to numbers !!! This is indeed brilliant because it is really simple : copy the "invisible" character (the pseudo-space) and paste it in the search !!! Thank you so much !
Excellent! It helped a lot. Thanks, thanks...
Super, thanks!
Thank you as this thread just fixed my problems. I would be interested to know what it was that caused that pseudo-space as it was clearly a space to me.
GREAT! That was exactly what I needed to do to erase the pesky space at the beginning of each cell in my sheet. Thanks!
You saved meeeee!!!!! many thanks
I literally cannot thank you enough for this! I spent over a half hour trying everything short of physically clicking each cell and removing the space after my numbers. This is, as another commenter said, brilliant!
Update - tested this same method with the Substitute formula - it works, too!
Nothing else was working - this did! Thankyou!
Great! I got it after trying with different methods as mentioned in this column. Thanks!
Is there a formula or a function that can be placed into a cell in excel that will automatically restrict space entery, at the time that data is being entered, to 1 space between characters.
Also how would I lock the formulas in cells, so they cannot be changed but allow data entery into the cell.
Many thanks
You can lock cells easily enough. Just right click the cell (or group of cells) and select format cells. Go to the protection tab and check the locked box. Note by default ALL cells are typically set. So really you have to unlock the cells you want to be able to change.
After you've made your sections go to file -> protect workbook. You can select a workbook, sheet whatever... once you've done that the cells will not be changeable unless you go back and undo the protection again via file -> protect. Optionally you can put a password on the protection to keep users out of modifications.
Just wanted to give my thanks. That SUBSTITUTE command just saved 2 days of data inputting in a 2000 rows spreadsheet.