Alok Paul
In this article, we will show how to remove unnecessary tab space from your Excel spreadsheet with 5 easy methods.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
5 Methods to Remove Tab Space from an Excel Spreadsheet
Let’s get introduced to the dataset first. The following dataset has 5 comments about 5 people. Somehow, it has some unnecessary tab spaces in them.
Now, we will learn 5 techniques to remove these useless tab spaces.
1. Use Excel CLEAN Function to Remove Tab Space
The CLEAN function removes all non-printable characters from the text. Non-printable characters in Excel mean Tab spaces, page breaks, line breaks, etc. The character codes for these are 127, 129, 141, 143, 144, and 157. However, the CLEAN function doesn’t require the character code for tab spaces. You just have to specify the data it has to clean. Yes, it’s that easy. Just follow the steps below for that.
Steps:
- First, add a new column named Cleaned Data in the dataset.
- Go to Cell C4 and put the following formula on that cell.
=CLEAN(B4)
- Now, pull the Fill Handle icon downwards.
We can see that all the cells of the Comments column are showing without spaces in the Cleaned Data column.
Read More: How to Remove Space between Rows in Excel (5 Methods)
2. Use the TRIM Function to Remove Tab Space
In this method, we will create a formula combining the TRIM, SUBSTITUTE, and CHAR functions. Let’s have a brief intro to these functions first.
The TRIM function removes all spaces from a text string except for single spaces between words.
The SUBSTITUTE function replaces existing text with a new text to a text string.
The CHAR function returns the character specified by the code number from the character set for your computer.
Now, execute the following steps.
Steps:
- Go to Cell C4. Copy the following formula and paste it into Cell C4.
=TRIM((SUBSTITUTE(B4,CHAR(9)," ")))
- Now, double click the Fill Handle icon.
Now, we can see the clean data in the Cleaned Data column.
Read More: How to Remove Space in Excel after Text (6 Quick ways)
Similar Readings
- How to Remove the Trailing Spaces in Excel (2 Easy Ways)
- Remove Space in Excel before Numbers (3 Ways)
3. Use Find and Replace Feature to Remove Tab Space
We have another interesting way to remove space from Excel. This is the Find & Replace feature of MS Excel. This tool first finds out the space and then removes them.
Steps:
- First, select all the cells of the Comments column.
- Choose the Find & Select tool of the Editing Then, select the Replace option.
We can also avail this option by pressing Ctrl+H.
- The Find and Replace window will appear.
- Go to the Find what and put Alt+ 0010 on that box.
Note:
Your keyboard must have a separate number pad to type 0010 after the ALT key. Otherwise typing Alt+0010 will not do anything.
- Then, go to the Replace with and put a spacebar on that box.
- Finally, click the Replace All option.
Now, look at the dataset.
All tab spaces are removed from the dataset. This Find & Replace method may not work in all versions of Microsoft Excel. In that case, you can try other methods.
Read More: How to Remove Blank Spaces in Excel (7 Ways)
4. Removing Tab Spaces Between Words with VBA
If you are a VBA freak like me and love to try VBA for every cause in Excel, I have a VBA code too for you to remove extra tab spaces from your dataset.
Steps:
- Go to the Sheet Name section at the bottom of the sheet.
- Choose the View Code option from the Context Menu.
- We enter the VBA Choose Module from the Insert tab.
- VBA command module appears now. We will write VBA code in this module.
- Now, put the following VBA code on the module.
Sub RemoveTabSpace()
   Selection.Replace Chr$(9), vbNullString
End Sub- Then, select the cells of the Cleaned Data column.
- Go to the VBA window again and press F5 to run the code.
- Look at the dataset now.
All the unnecessary spaces have been removed already.
Read More: How to Remove Spaces in a Cell in Excel (5 Methods)
5. Removing Extra Spaces with Power Query
The Power Query is another interesting tool to remove tab spaces. Please follow the steps for that.
Steps:
- Select all the cells of the Comments column.
- Choose the From Table/Range option from the Data tab.
- A pop-up window appears to show the selected range. Check the My table has headers option.
- Finally, press OK.
- The Power Query window will appear now.
We can see there are tab spaces that exist in the dataset.
- Now, go to the heading of the column.
- Press the right button of the mouse.
- Go to the Transform option from the menu.
- Now, click on the Clean option.
- Look at the column now.
All the tab spaces have been removed.
Read More: How to Remove Space after Number in Excel (6 Easy Ways)
Conclusion
In this article, we showed how to remove tab space from Excel. We showed 5 different methods for this. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
