by 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 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)
. Then just press V.
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
264 comments
Try this formula =NUMBERVALUE(SUBSTITUTE(TRIM(TEXT(A2,"0.00"))," ","")) - I figured out today when i had to manage really misaligned data such as this : 3
4
0
. 0 - for a value of 340.00 in amount column
Hello!
I have a document of data that was an output from a software we use. The main column of data that we need seems to have all been imported as 5 spaces, a 3 digit number and 1 space. Because of the spaces, we arent able to use the cell in a formula to get the information we need.
We have tried the trim, substitute and replace tools but the spaces wont go!
Does anybody know how we can get rid of the spaces?
Thank you
Hello! If the methods written in the article above didn't work for you, try these recommendations: How to remove blank spaces in Excel - leading, trailing, non-breaking.
I also recommend checking out Trim Spaces tool, which can help you remove unnecessary spaces without using formulas in a couple of clicks. This tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hi Everyone,
You just remove your spaces by using flash fill function.
For Example:
Column A have original data with spaces.
Step 1: Type same number in which (A1) cell have in B1 cell.
Step 2: Then Type same number in which (A2) cell have in B2 cell, here you will see blur bottom line, showing all the numbers in which column A have, just hit the TAB button. and your data is clean with all spaces.
Sorry, I do not fully understand the task.
As it's currently written, it's hard to tell exactly what you're asking.
Thank you it works perfectly!
I am using someone elses spreadsheet with a load of imported/pasted data in it. I need to alter the numbers in the cells from output per hour to output per day. So essentially all i need to do is, for each cell *8, for 8 hours.
Unfortuantly, each cell with numbers in it seems to have a 'space' after each number. This presents an error in the formula and will not times the data by 8. When i manually remove the 'space', everything works as it should.
We are talking about thousands of cells i need to manually remove the 'space' after each bit of data in each cell. I have tried the methods above without success.
Any help would be greatly appreciated.
And by 'after each number' i mean there is a space at the end of the data in each cell.
Hi! You can convert numbers written as text to normal numbers by using the formula
=--SUBSTITUTE(A1," ","")
We have a tool that can solve your task in a couple of clicks without formulas. I'd recommend you to take a look at our Convert Text tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
I copied a list of dates from a site, however they pasted as 'general' text in my spreadsheet as there is a space at the end of each date. Ultimately I want to organise the dates by 'oldest to newest'. Selecting the column and changing it to 'date' doesn't do anything, I imagine because of the extra space at the end. I tried TRIM, SUBSTITUTE and replace all and none of it got rid of the spaces for me (it only seems to work on spaces in between words/numbers, but not at the end). As the list is 1200+ entries long it's a bit much to manually delete the spaces. Does anyone have a solution for this? Many thanks in advance
Hi! Unfortunately, without seeing your data it is difficult to give you advice. I recommend reading this guide: How to convert text to date in Excel.
how can i remove single spaces
I have data in different cells in excel sheet as "D46D47E20HE20HE20ME38" how i add comma after each numbers in the cell.
Hi! I'm really sorry, looks like this is not possible with the standard Excel options. Try to use VBA.
If you have a phone number in format 0X XXX XXX XXX, how do you remove spaces without dropping the zero at the start?
Hello! To remove spaces, you can use the SUBSTITUTE function. To leave a leading zero, use the TEXT function and these guidelines: How to add leading zeros in Excel with the TEXT function.
=SUBSTITUTE(A1," ","")
=TEXT(SUBSTITUTE(A1," ",""),"00000000000")
Thank you so much for this content. It has greatly helped me to reduce errors while working with large data especially when comparing items in two different columns
Thank you đ
Awesome!!
I have copied a table from outlook & there having some junk characters which are looks like space but not space actually.
All ways for clean the value column are done, number format also applied but still calculation not possible.
can you please support for the problem.
Hi! I hope that in this article you will find the answer: How to remove spaces in Excel - leading, trailing, non-breaking.
How can I get rid of leading and trailing spaces in a cell with multiple lines of text? The TRIM function works for a cell with just one line of text
Hi!
I could not repeat your problem. The TRIM function works in a cell with multiple lines of text. Maybe this article will be helpful: Excel TRIM function - quick way to remove extra spaces.
Thank you so much!! Your tips saved me time from having to update, and I count...5636 rows of data!!!!! That would have taken me days to update.
SO much Thanks with Respect and Gratitude
Thanks a lot. it was a big help.
You are a god whoever made this page; thank you so much you saved me so much time with space trimming
I am looking for some help. I have a column of numbers with a ' before each number, however, there are a bunch of them that have what looks like a space and it is not tight against the left edge. I have tried the Trim function, but that does nor work. I am not sure as to how to fix this.
Thank you
Hello!
I'm assuming you want to convert text to numbers. You can use a math operation or another method described in this article. If this does not suit you, please explain what you want to do.
I have an excel sheet which is received from my bank but amount column having spaces in start of the numbers i put the formula of SUBTITUTE and TRIM but cannot sum of total value of amount by selecting of column.
Hello!
To convert text in the form of a number with a space to a regular number, use the VALUE function, as well as other methods described in this tutorial: How to convert text to number with formula and other ways in Excel.
Thanks, 3rd option im use