During working on any large worksheet you can see lot of spaces between the text strings. You need to remove spaces between the characters and numbers within a cell in Excel. In this guide we have to discuss quick ways to remove spaces from a given text string in Excel.
When you copy large amount of data or try to generate a report from any software, you will take lot of time to find spaces between the characters and numbers within a cell. If you try to remove the given space manually you have to take lot of time to prepare the correct report. So you must know how to remove leading and trailing white spaces from given string in Excel.
Quick Way How to remove spaces from a given text string in Excel
There are billion of users around the world who are daily working on the Excel sheets. If you have any report including lot of spaces between the characters that you must need to know Quick methods to remove spaces from a given text string in Excel sheet. Due to leading and trailing spaces the format of the report is unusable for us. To set the proper format of that report you have to apply given three quick ways to remove all leading and trailing spaces from text string in Excel.
- Quick methods to get age from birthdate in Microsoft Excel
- How to use Microsoft Excel LEN function to know length of text string
Method 1: Remove Spaces from TRIM function in Excel
TRIM is an important text function of Microsoft Excel. TRIM function allows you to quickly remove all leading and trailing spaces from given text string in Excel sheet. To delete spaces from a given text string in Excel, you must have to take given simple steps.
Step 1: Open an existing worksheet or create the following sheet in which you have lot of leading and trailing spaces in the text string.
Step 2: If you want to remove spaces from a given text string in Excel then you have to apply given Trim function in the cell =TRIM(A1) and press enter.
Step 3: Now, copy or drag the formula of cell A1 till rest of the cells in active worksheet with the help of fill handle button. You can also copy the formula by selecting the cell A1 till rest of the cells and press CTRl+D shortcut key to view the result.
Method 2: Remove Space using Find and Replace in Excel
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. Select the range of cells from which you want to remove spaces from text string in Excel.
- How to Copy Formatting only using Fill Handle in Excel
- How to compare data between two lists in Excel
Step 2: 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 shorcut key to directly open the “Find and Replace” dialogue box.
Step 3: 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 3: Remove Space using Substitute function in Excel
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. Take the cursor on another location where you want to get the result without any spaces.
Step 2: Type the following given function on cell A1 =SUBSTITUTE(A1,” “,””) and press enter to get the result.
Step 3: Again select the cell A1 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.
I hope after reading this guide you can easily remove spaces from a given text string in Excel. Given three method helps you to quickly remove all leading and trailing spaces between the characters and numbers in active worksheet. If you have any query or suggestion regarding this guide than please write us in the comment box. Thanks to all.