If you would like to remove all spaces from your text in Excel, it can be done quickly and easily.
Follow the steps below to learn how:
- In the cell where you would like your text without spaces, key in =SUBSTITUTE(.
- Select the cell that contains the text with spaces.
- Key in a comma to move to the next argument, and then key in ” “ making certain to include the space between the double quotation marks. This is important because this is the part that tells Excel to replace the spaces in your text.
- Key in a comma again, to move to the next argument and then key in “” making sure there is NO SPACE between the double quotation marks this time.
- Click Enter and that should take care of it for you.
To apply this to your 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. This will effectively copy the function down the entire list.
Take note that all spaces have been removed from all examples, regardless of how many spaces were in the original example.
So, now you have a new list next to your old one. The new list, thought, is just a function and no actual text. To turn your new column into an actual list of text, simply select it, click CTRL + E + S +V and then click Enter.
This will turn the SUBSTITUTE function into the text it outputs and you will now have a nice new list!
So now that you know how to do this, we can take it a step further. How about if you want to remove certain spaces from your text, but not all of them?
The steps below will tell you how to remove a specific instance of a space (i.e., the second space removed from your list of data).
- Key in =SUBSTITUTE( in the cell where you want the text to appear, as above.
- Select the text where you want to remove the spaces.
- Key in a comma and then key ” “ making sure you have a space between your double quotation marks.
- Key in a comma and then key in “” making certain there are NO SPACES between your double quotation marks.
- Key in a comma to move to the last argument of your SUBSTITUTE function. Now we will tell the function which occurrence of spaces you want to remove. I you key in a 1, then the first space will be removed, but if you key in a 2, the second occurrence of a space will be removed. For our example though we will key in a 2.
- Click Enter.
You will now notice that the space has been removed in that one instance.
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 your whole list.
So once again, you now have a new list next to your old one. The same rules apply as above regarding it being a function instead of actual text, so follow the same steps.
So now you are almost a pro at this space removing! So let’s learn a little bit more shall we?
You can also remove spaces from text in a Selection, an entire Worksheet or your entire Workbook at once!
Follow the steps below to learn how:
- If you want to remove spaces from only a selection of data, then select the data now. If you want to apply it to your entire worksheet or workbook do not select any data and follow the next step.
- Click CTRL + F.
- Go to the Replace tab.
- In Find what, key in a single space.
- It may look as though there is nothing there, but there is.
- If you made a selection of cells in the first step above, you can simply click the Replace All button now and you will be finished.
- A small window will open telling you how many replacements were made. If you did not make a selection, then move to the next step.
- Click Options.
- You will see many options, but you only need to look at one of them. Where it says Within select if you want to replace all spaces within the current worksheet by selecting Sheet or replace all spaces in your entire workbook by selecting Workbook.
- Once you have made your selection, you can choose Replace to Replace one by one, or the quicker method of Replace All.
Everyone in the office is going to think you are a regular Excel guru now!