3 Methods to Remove Spaces Between Text in Excel

Add to Favorites
Author: | Edits: don

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.

Sections:

Quickly Remove All Spaces from Text in Excel

Remove Certain Spaces from Text in Excel

Remove Spaces from Text in a Selection, Entire Worksheet, or Entire Workbook at Once

Notes

Quickly Remove All Spaces from Text in Excel

=SUBSTITUTE(A1," ","")
  1. Type =SUBSTITUTE( in the cell where you want the text without spaces to appear.
    a66de7ddfe11add6b05304a282289d29.png
  2. Select the cell that contains the text with spaces.
    ec7ea57b349b2878a213934481bcf2ac.png
  3. 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.
    d6cb9ad76456d7d78fba46c89b79e37e.png
  4. 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.
    b0f8cd2786ca6860f6a46adb9769bd78.png
  5. Hit Enter and that's it!
    bb3a39cc6019073f54201e3131e06719.png
  6. 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.
    71100fe9151daa3447e38dbb9b223710.png

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)
  1. Type =SUBSTITUTE( into the cell where you want the text to appear.
    71185f6fa937d467ab91fa760ff0c024.png
  2. Select the text from which you want to remove spaces.
    a4790a078776d62369194eee2f4de4ec.png
  3. Type a comma and then input " " making sure to have a space between the double quotation marks.
    04646df80a2de65f13113b65806ad798.png
  4. Type a comma and then input "" making sure to NOT have anything between the double quotation marks.
    a6261c3a3f532efe628dc8de1b3086f4.png
  5. 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.
    f0a4a642f91554eece0c3be138bee939.png
  6. Hit Enter and that's it.
    61d700aac38cfc7e1c44192a9ca58bed.png
    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.
  7. 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.
    801ea87aad9030258ddf2431b8e9ea2b.png

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.

  1. 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.
    9fbe436040c0fd966ebaa47e64ca5799.png
  2. Hit Ctrl + F on the keyboard.
    b6e019dd1bde33db95d1088d7e7e4dcb.png
  3. Go to the Replace tab.
    0bcacc8a698729f83b04306cc8a1caf4.png
  4. In Find what type a single space:
    16ff69ae117ea5392efb0c36f97f88de.png
    It is difficult to see, but there is a single space in there.
  5. 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)
    2499abc3bc50f7f69a3bf393393de97e.png
  6. Click Options > >
    5c7f2154cedec90c678e8cf326e8e2ed.png
  7. 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.
    3f3034c590bf9ca19f49419dd304ca8e.png
  8. 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.
    2a402f8f52dbcdc903c0da1c00d5678d.png
  9. Hit OK and then close the Find and Replace window and you can see the result.
    bc91cfdecdf553052db6f7cf61867a4c.png

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.


Excel Function: SUBSTITUTE()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Remove All HTML from Text in Excel
Tutorial: How to quickly and easily remove all HTML from data copied into Excel. This tutorial inclu...
Format Cells as Text in Excel
Macro: This free Excel macro formats a selection of cells as Text in Excel. This macro applies th...
Convert Numeric Dollar Values into Text in Excel - UDF
Macro: Convert a numeric dollar amount into its text equivalent. This UDF (user defined function)...
Prevent Spaces from Being Entered in Cells in Excel
Tutorial: How to prevent a user from entering any spaces within text in Excel.  This allows you to...
Guide to Combine and Consolidate Data in Excel
Tutorial: Guide to combining and consolidating data in Excel. This includes consolidating data from ...
Convert Month Name to Number and Vice Versa in Excel
Tutorial: How to convert a month's name, such as January, into a number using a formula in Excel; al...
Tutorial Details
Excel Function: SUBSTITUTE()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course