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
Very useful, thanks!
12132637
12132632
12132431
12130179
how can I delete/remove these spaces, that were copied from Outlook.
I have applied all aforementioned methods however could not get thru.
Hi!
You can find the examples and detailed instructions here: How to remove spaces in Excel - leading, trailing, non-breaking.
Thank You Very much it's help full
kindly avail us the sample to practise
how to remove space after the number ie 123
Hi!
Where are the spaces in this number? Read the article above carefully.
Thank you 4 your tips
Thank you
Item Base price Disc @ 16.75% Net price after Disc ~CGS for January/ Pair Royalty @1% SBU OH Cost per unit for SBU GP GP% NP NP% Net profit/ (Loss) for production taken in DEc and Jan(20-21)
1. 8143 MRN 5X9 152 25.46 126.54 123.78 1.27 19.00 144.05 1.49 1% -16.24 -13% -80,810
2. DG9800 N.BLU 6X10 189.00 31.66 157.34 153.88 1.57 19.00 174.45 1.89 1% -15.54 -10% -1,97,388
3. DG9804 BLK 6X10 183.00 30.65 152.35 143.23 1.52 19.00 163.75 7.59 5% -9.88 -6% -1,08,223
4. DL3011 BLK 5X9 140.00 23.45 116.55 98.42 1.17 19.00 118.58 16.97 15% -0.87 -1% -11,338
5. DL3411 N.BLU 5X9 128.00 21.44 106.56 92.14 1.07 19.00 112.21 13.35 13% -4.58 -4% -75,717
6. DL3703 BLU 5X9 164.00 27.47 136.53 108.03 1.37 19.00 128.40 27.13 20% 9.50 7% 68,315
Please specify the how to remove the space
Hello!
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.
The title of the article "3 ways to remove spaces between words / numbers in Excel cell". You then go on to explain How to remove EXTRA spaces. For those of us looking to do what your title says, remove (ALL) white spaces the article is useless.
The way to do this, and to answer the question about removing the "-" character, is to use the substitute command. Assuming the original text is in cell B3, the command would be, =substitute(B3, " ", "") .
Any character put in as the second argument will be removed. For exmample, the get rid of dashes the command is, =substitute(B3, "-", "").
To get rid of commas the command is, =substitute(B3, "," , "")
Hi,
You may not have noticed, but the replacement function is described in the article above - Using formula to remove all spaces.
Select the columns and press ctrl+H
Find=(-)
Replace (keep Black) then Replace All
Hello !
Can anyone suggest me how to convert my below data from different rows to sequential rows without any space/blank row. I want to know to how can we remove multiple rows in a single command/formula.
RadioSrNumber
EIC3820915MHZ-000108
RAD915MHz-000108
EIC3820915MHZ-000109
RAD915MHz-000109
EIC3820915MHZ-000454
RAD915MHz-000454
EIC3820915MHZ-000499
RAD915MHz-000499
EIC3820915MHZ-000519
RAD915MHz-000519
EIC3820915MHZ-001149
RAD915MHz-001149
EIC3820915MHZ-001457
RAD915MHz-001457
I wan to covert this above mentioned data into data as per below format
EIC3820915MHZ-000108
EIC3820915MHZ-000109
EIC3820915MHZ-000454
EIC3820915MHZ-000499
EIC3820915MHZ-000519
EIC3820915MHZ-001149
EIC3820915MHZ-001457
Hello!
You cannot delete rows using an Excel formula. We have a tool that can solve your task in a couple of clicks - Ablebits Tool - Select by Value. After that, you can delete the rows with the selected cells. Use menu Home - Cells - Delete Sheet Rows.
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.
If you have any other questions, please don’t hesitate to ask.
when i remove a space in a phone number e.g 087 9876543 i am getting 879876543- the space is gone but so is the initial 0. any fix
Hello!
You didn't say how you removed the space from the number. If you use the SUBSTITUTE function (see example above), the result will be the text "0879876543".
The same result is obtained using Ultimate Suite for Excel: Ablebits Data - Remove Characters.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
how to add a space in this and make it a proper statement ?
Can anybody suggest?
How can I trim spaces from "selected cells" (not an entire column) - I have a spreadsheet where I only want to trim spaces from selected cells, within one column, but not all. Is there a way of selecting the cells, by using CTRL + select, etc., without removing spaces from non-selected cells?
Hello!
We have a tool that can solve your task in a couple of clicks.
The way you are talking about is possible if you use Ablebits Data - Trim Spaces.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Your way to remove spaces is not possible with standard Excel tools. If you are using the TRIM function that only works on one cell, then apply it only to the desired cells. If you use "Find and Replace", then with the button Find Next, skip individual cells.
how to remove space like
A 25052
Hello!
I hope you have studied the recommendations in the above tutorial.
The formula below will do the trick for you to remove space:
=SUBSTITUTE(A1," ","",1)
best solution i have found so far
=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))
That's the one!
Thank you!
Thanks.
Great, Thanks. its a perfect solution.
This really works. Thanks!
I have this information in one cell (column A)
VALLEJO - A52
I need to delete - A52 from column A and copy the A52 into column B.
So this is the final product
A52 (Column B) Vallejo (Column A)
Hello Jeanette!
How to split text into cells - read here.
We have a tool that can solve your task in a couple of clicks: Ablebits Data - Split text.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Lookout for non breaking spaces!
It looks like a normal space but it is not.
Find and replace using a normal space character does not work!
One has to find #(00A0) and replace it!.
Any idea how to remove a space after the number, for example 150.44__
The __ is where my space is
Thank you
Hello Lisa!
Which of the recommendations written in the article above did you use? What problems and errors occurred?
We have a tool that can solve your task in a couple of clicks: Ablebits Data - Remove Characters. This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Hey
How do you remove spaces from a cell that contains numerical data quickly when all of the cells have different amounts of spaces before them.
For example “ 550”, “ 300”, “ 6000000”
Hello!
I hope you have studied the recommendations in the above tutorial. Please specify what formula you used and what problem or error occurred.