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.


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)

Excel CLEAN Function to Remove Tab Space

  • Now, pull the Fill Handle icon downwards.

Excel CLEAN Function to Remove Tab Space

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)," ")))

TRIM Function to Remove Tab Space

  • 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


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.

Find and Replace Feature to Remove Tab Space

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.

Find and Replace Feature to Remove Tab Space

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.

Removing Tab Spaces Between Words with Excel VBA

  • We enter the VBA Choose Module from the Insert tab.

Removing Tab Spaces Between Words with Excel VBA

  • 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.

Removing Tab Spaces Between Words with Excel VBA

  • 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.

Remove Extra Tab Spaces with Excel Power Query

  • A pop-up window appears to show the selected range. Check the My table has headers option.
  • Finally, press OK.

Remove Extra Tab Spaces with Excel Power Query

  • 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.

Remove Extra Tab Spaces with Excel Power Query

  • 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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo