How to remove spaces from the middle of text in Excel. This includes removing all spaces from the middle of text in a cell and removing only certain spaces from the middle of text in a cell.
The methods shown below are easy to use and can be quickly applied to any size list or the entire workbook, which will save you a lot of time.
Type =SUBSTITUTE( in the cell where you want the text without spaces to appear.
Select the cell that contains the text with spaces.
Type a comma to move to the next argument and then type " " making sure to include the space between the double quotation marks. This is the part that tells Excel to replace the spaces in the text.
Type a comma again to move to the next argument and then type "" making sure that there is NO SPACE between the double quotation marks this time. This is what Excel will replace the spaces with, nothing.
Hit Enter and that's it!
To apply this to the entire list, simply double-click the small black square in the bottom right of the cell that you can see when cell B1 is selected in the above image; this effectively copies the function down the entire list.
Notice that this has removed all spaces from all of the examples, regardless of how many spaces were in the examples.
You now have a new list next to the old one. The new list is, however, just a function and not actual text. To turn the new column into an actual list of text, simply select it, hit Ctrl + C, then hit Alt + E + S + V and hit Enter. This will turn the SUBSTITUTE function into the text it outputs and you will have your nice new list.
Remove Certain Spaces from Text in Excel
This method allows you to remove a specific instance of a space; for instance, maybe you need only the second space removed from a list of data.
=SUBSTITUTE(A1," ","",2)
Type =SUBSTITUTE( into the cell where you want the text to appear.
Select the text from which you want to remove spaces.
Type a comma and then input " " making sure to have a space between the double quotation marks.
Type a comma and then input "" making sure to NOT have anything between the double quotation marks.
Type a comma to move to the last argument of the SUBSTITUTE function. Here, we tell the function which occurrence of "spaces" we want to remove. If I put a 1, then the first space will be removed; if I put a 2, the second occurrence of a space will be removed. Here, I want the second occurrence to be removed so I put a 2.
Hit Enter and that's it.
Notice that the space between the 3 and the c has been removed but the space between the c and the 1 has been left in-tact. This is because I put a 2 in for the last argument, so only the second occurrence of the space was removed.
Copy the function in cell B1 down the list by double-clicking the bottom right corner of that cell after you select it and it will be applied to the entire list.
You now have a new list next to the old one. The new list is, however, just a function and not actual text. To turn the new column into an actual list of text, simply select it, hit Ctrl + C, then hit Alt + E + S + V and hit Enter. This will turn the SUBSTITUTE function into the text it outputs and you will have your nice new list.
Remove Spaces from Text in a Selection, Entire Worksheet, or Entire Workbook at Once
This method uses Find and Replace. The benefit of it is that you can quickly apply it to an entire worksheet or the entire workbook at once.
If you want to remove spaces from just a selection of data, then select that data now. Otherwise, if you want to apply it to an entire worksheet or the entire workbook, don't select any data and skip to Step 2.
Hit Ctrl + F on the keyboard.
Go to the Replace tab.
In Find what type a single space:
It is difficult to see, but there is a single space in there.
If you made a selection of cells in Step 1, then you can simply hit the Replace All button now and you will be done. A small window will open telling you how many replacements were made. (If you did not make a selection of cells in Step 1, then you can skip to Step 6)
Click Options > >
Here, you will see a lot of options, but you only need to look to one of them. Where it says Within choose if you want to replace all spaces within the current worksheet by selecting Sheet or replace all spaces in the entire workbook (all worksheets) by selecting Workbook.
Once you have made your choice, you can choose Replace to replace one by one, which takes forever and is not recommended unless you have some data where no replacements should be made, or you can choose Replace All, which is what I will do. Once I click that button, a small window opens showing how many replacements were made.
Hit OK and then close the Find and Replace window and you can see the result.
There are a lot of steps to this method but, once you understand what you are doing, it will only take a few seconds to use this method.
This method is really helpful because it can be quickly applied to an entire worksheet or the entire workbook at once. Also, this method eliminates the need to erase the original list and replace it with a new one.
Notes
All of the methods illustrated above are good methods and you should use them depending on the scenario. The SUBSTITUTE method is a bit more surgical and using it will help to prevent you from removing spaces where you should not, but the Find and Replace method can be applied to a lot of data across multiple worksheets at once with ease.
Make sure to download the accompanying spreadsheet for this tutorial so you can try these methods out and see them in action.