How to Remove Tab Space from Excel (5 Easy Methods)

In this article, we will show how to remove unnecessary tab space from your Excel spreadsheet with 5 easy methods.

How to Remove Tab Space from an Excel Spreadsheet: 5 Simple Methods

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.

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.

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.

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.

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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Remove Tab Spaces.xlsm

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 give your suggestions and feedback in the comment box.

<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Remove Tab Space from Excel (5 Easy Methods)

How to Remove Tab Space from an Excel Spreadsheet: 5 Simple Methods

1. Use Excel CLEAN Function to Remove Tab Space

2. Use the TRIM Function to Remove Tab Space

3. Use Find and Replace Feature to Remove Tab Space

4. Removing Tab Spaces Between Words with VBA

5. Removing Extra Spaces with Power Query

Conclusion

Related Articles

Leave a reply Cancel reply