Plain text reports, figures from websites, and other sources of data that you paste into an Excel spreadsheet are likely to have additional spaces in addition to the crucial data. Leading and trailing spaces, multiple spaces between words, and thousand separators for numbers are all permitted.
Your table looks disorganized and is now challenging to utilize. Although leading gaps and the spaces between values are visible to the human eye, they can sometimes be challenging to identify when working with massive amounts of data. Trailing spaces are difficult to locate.
In this tutorial, we will explore different ways to remove spaces in Excel, including using various functions and find and replace feature. By the end of this tutorial, you will have the skills to effectively remove spaces in Excel and improve the accuracy and presentation of your data.
This Tutorial Covers:
- Why remove space in excel
- Using TRIM function
- USING SUBSTITUTE FUNCTION
- Using Find and Replace option
- Using CLEAN function to remove line breaks
- Why remove space in Excel?
There are several reasons why it is important to remove spaces in Excel:
Consistency: Spaces in data can make it difficult to maintain consistency and accuracy in your spreadsheet. For example, if you have a column containing the names of people, having extra spaces in some of the cells can cause issues while sorting or filtering data.
Calculation errors: Spaces can cause issues while performing calculations in Excel. For instance, if you have a column containing numbers with spaces in some cells, Excel may treat those cells as text and result in errors while performing calculations.
Formatting: Extra spaces in cells can also affect formatting in your spreadsheet. It can cause cells to appear wider than necessary, making it difficult to read or print the data.
Data import/export: When you import or export data to and from Excel, extra spaces can cause issues. It may affect the ability of other programs to read or interpret the data correctly.
Therefore, removing spaces from your data in Excel can help you maintain consistency, accuracy, and formatting in your spreadsheet, and ensure that your calculations are performed correctly.
- Using TRIM function:
You may conduct many operations with the aid of Excel’s numerous functions and formulae.
Use the TRIM tool to eliminate extra spaces between texts.
The surplus leading, trailing, and in-between space is eliminated by this function. The two texts will be separated by a space, nevertheless. For the trimmed values, you will need an empty column.
Take a look at this illustration with various arrangements of leading, trailing, and in-between gaps.
The steps to remove extra spaces in Excel using TRIM Function are described below:
Step 1: Your data should include the helpful column at the end. It can be called “Name with Trimmed”.
Step 2: Enter the formula to remove extra spaces in the first cell of the helper column (B2).
=TRIM(A2)
Step 3: Utilize the auto fill handler to fill the remaining cells. The outcome is shown below:
The trimmed values are then created and added to the helper column. By copying and pasting the trimmed values to the desired location, you can replace the untrimmed data.
By selecting cut/copy from the context menu of the right-clicked column you can copy the reduced values. Use the paste special to paste the values for the shortened gaps when pasting.
3. USING SUBSTITUTE FUNCTION:
This approach should be used if you wish to eliminate all the gaps that are connected to numbers or text. You may eliminate all the gaps in your data by employing a straightforward method.
The idea behind this technique is to substitute an empty string for the space.
The steps to remove extra spaces in Excel using SUBSTITUTE Function are described below:
Step 1: Your data should include the helpful column at the end. It can be called “Trimmed Value”.
Step 2: Enter the formula to remove extra spaces in the first cell of the helper column (B2).
=SUBSTITUTE(A2,” “,””)
The cell A2 in this instance that holds the untrimmed value. That an empty string value (“”) is used in place of the space character.
Step 3: Utilize the auto fill handler to fill the remaining cells. The outcome is shown below:
4. Using Find and Replace option:
Another way to eliminate superfluous spaces between words or numbers is to use this technique. The idea behind this technique is to count the number of spaces and then replace them. The leading, trailing, or in-between space is left out using this method, though. When the untrimmed data are dispersed more evenly throughout the spreadsheet, this technique works quite well.
This technique has the benefit of replacing the values in the same location. For the trimmed values, there is no need to introduce a new, empty column.
The steps to remove extra spaces in Excel using Find and Replace option are described below:
Step 1: Choose all the datasets but the header.
Step 2: Go to the “Home” tab, select “Editing,” then click “Find & Select” and “Replace.” Alternately, just hit Ctrl+H on your keyboard.
Step 3: A “Find and Replace” dialog box appears as a result.
Double-space (” “) and a single space (” “) should be entered in the “Find what” and “Replace with” text boxes, respectively.
Then select “Replace All.”
Step 4: A notification displaying replacements done for a specific number of locations appears as a result. Select OK.
Step 5: Perform the “replace all” action repeatedly until a message appears indicating that no more replacements can be made, typically shown as “We couldn’t find anything to replace”.
After that, the result looks like below:
5. Using CLEAN function to remove line breaks:
The CLEAN function in Excel is a text function that removes any non-printable characters from a text string. This function is useful when dealing with text that may contain hidden formatting or characters that cannot be printed, such as line breaks or tabs.
Suppose we have a dataset like the one below and want to remove line breaks from it.
The steps to remove line breaks in Excel using CLEAN function are described below:
Step 1: Your data should include the helpful column at the end. It can be called “Trimmed Address”
Step 2: Enter the formula to remove line break in the first cell of the helper column (B2).
=CLEAN(SUBSTITUTE(B2,CHAR(10),”, “))
Note: The SUBSTITUTE function is used in the formula to replace the line break character (represented by CHAR(10)) with comma and space character (“, “). If you want to replace the line break with a different character or no character at all, you can modify the formula accordingly.
Step 3: Utilize the auto fill handler to fill the remaining cells. The outcome is shown below:
The trimmed values are then created and added to the helper column. By copying and pasting the trimmed values to the desired location, you can replace the untrimmed data.
By selecting cut/copy from the context menu of the right-clicked column you can copy the reduced values. Use the paste special to paste the values for the shortened gaps when pasting.
After completing these steps, the table should display the contents of the original column without any line breaks.
Removing extra spaces in Excel can be done using several methods such as TRIM, SUBSTITUTE, CLEAN functions, and Find and Replace feature. It’s important to be mindful of the potential impact on data integrity and formatting when removing spaces from text. It is recommended to carefully review the data and use appropriate methods to remove spaces while ensuring the resulting data is still accurate and meaningful.
For ready-to-use Dashboard Templates:
