How to remove spaces from text string in Excel

Sometime when you are working on the large database you need to remove spaces between the characters and numbers within a cell in MS Excel. This article helps you how to quickly remove spaces from text string in Excel.

When you copy any data or generate report from any software, you will get lot of spaces between the characters and numbers within a cell. If you try to remove these space manually you have to take lot of time to prepare the correct report. In this guide we can discuss few simple methods which helps you to remove spaces from text string in Excel.

Must Read: Quickly delete blank rows and columns in MS Excel

Steps to remove spaces from text string in Excel

There are billion of users around the world working on the MS Excel. Sometime you when you get any report which have lot of spaces between the characters. In that format report are unusable for us. If you want to remove spaces from the text string in Excel, you have to use given methods.

How to remove spaces from text string in Excel

Method 1: From Find and Replace:

All we know Find command is used to search any text or number in the worksheet. Replace command is used to find any text or number along with change them with others as per your need. In this guide we have to take help of “Find and Replace” command to remove spaces from text string in Excel. You have to take few simple given steps:

Step 1: Open the active worksheet which has lot of spaces in the text string.

Step 2: Select the range of cells from which you want to remove spaces from text string in Excel.

Step 3: To remove all the spaces click on the Home tab > Click on “Find & Select” option > Now, click on the “Replace” option. You can also press Ctrl+H to directly open the “Find and Replace” dialogue box.

Step 4: Press space bar single time in the “Find What:” box and click on the “Replace all” button. There is no need to fill any value in “Replace with:” box.

Method 2: From Substitute function:

Substitute is an important text function of Microsoft Excel. Substitute function allows you to replace existing text with new text in a text string. You have to take few simple steps to remove spaces from text string in Excel.

Step 1: Open the active worksheet which has lot of spaces in the text string.

Step 2: Take cursor on another location where you want to get the result without any spaces.

Step 3: Type the following given function on cell A9 =SUBSTITUTE(A1,” “,””) and press enter to get the result.

Step 4: Again select the cell A9 and drag from fill handle up to required range of cell. Now, you will get all leading, trailing and middle spaces are removed from the active worksheet.

Must Read: 3 Ways to quickly find duplicate values in Excel

I hope after reading this guide you can easily remove spaces from text string in Excel. Both method helps you to quickly remove all spaces between the characters and numbers in active worksheet of Excel. If you have any query than write us in the comment box. Thanks to all.

Leave a Reply

Your email address will not be published. Required fields are marked *