Find and Replace Tab Character in Excel (2 Suitable Ways)

Tab character plays a vital role in Excel usage. With this character, we can quickly create four spaces in one hit. But, during data analysis and presentation, we need to find and replace these tab characters sometimes. If you are looking forward to the ways of doing this, you have come to a perfect place. In this article, I will show you 2 suitable ways to find and replace tab character in Excel.


Download Practice Workbook

You can download our practice workbook from here for free!


2 Ways to Find and Replace Tab Character in Excel

Say, you have 5 inputs where the inputs possess tab character.ย  Now, you want to find and replace these tab characters. You can go through the following article below and use any of the given ways to accomplish your result easily.

Inputs with Tab Character

In this article, we have used the Office 365 version of Microsoft Excel. But, no worries. You can apply these ways in any other version of Excel. If you face any problems regarding versions., please leave a comment below.


1. Using Find and Replace Dialogue Box

The quickest way to find and replace tab character in Excel is to use the Find and Replace dialogue box. Follow the simple steps below to accomplish this.

๐Ÿ“Œ Steps:

  • First and foremost, create a new column named Output to get your result.
  • Afterward, select the inputs (B5:B9) and click your right mouse button.
  • Following, choose the option Copy from the context menu.

Copy the Inputs to Find and Replace Tab Character in Excel

  • Subsequently, select the C5 cell and right-click on your mouse.
  • Subsequently, choose the Paste option from the context menu.

Paste the Copied Cells

  • Now, select the output cells (C5:C9) >> go to Home tab >> Editing group >> Find & Select tool >> Replaceโ€ฆ option.

Choose Replace Option to Find and Replace Tab Character in Excel

  • As a result, the Find and Replace dialogue box will appear.
  • Subsequently, go to the Replace tab >> type Alt+0009 in the Find what: text box >> type Space Bar in the Replace with: text box >> click on the Replace All button.

Find and Replace a Tab character with Spacebar in Excel

Consequently, you will see that all the tab characters are found and replaced with a spacebar. And, for example, the output should look like this.

Output without Tab Characters

Note:

Here, you must type the 0009 number on the Numpad keyboard.

Read More: How to Insert Tab in Excel Cell (4 Easy Ways)


2. Utilizing a Text Editor to Find and Replace Tab Character

Sometimes, we might face problems with some Excel versions when using the Find and Replace option. For those versions, we can utilize any text editor to find and replace tab character in Excel.

๐Ÿ“Œ Steps:

  • Initially, copy the inputs (B5:B9) containing tab characters by right-clicking and selecting the Copy option from the context menu.

Copy Input Cells

  • Now, open any text document on your device.
  • Afterward, right-click inside the text editor and choose the Paste option from the context menu.

Paste the Copied Cells in Text Document to Find and Replace Tab Character in Excel

  • As a result, you will see that the inputs are inside the text editor now with tab characters.

Inputs with Tab Characters

  • Now, select the tab character from any input >> right-click your mouse >> choose the Copy option from the context menu.

Copy Tab Character

  • Afterward, press Ctrl + H to open the Replace window.

Bring the Replace Window to Find and Replace Tab Character in Excel

  • Afterward, Paste the selection inside the Find what: text box >> hit the Spacebar in the Replace with: text box >> click on the Replace All button.

Find and Replace Tab Character in Excel

  • Consequently, all the tab characters will be found and replaced by a space in the text editor.

Text Document without Tab Characters

  • Now, select the lines from the text editor and right-click on your mouse.
  • Following, choose the Copy option from the context menu.

Copy the Result from Text Editor

  • Afterward, go to the Excel file, and at the C5 cell right-click your mouse.
  • Subsequently, choose the Paste option from the context menu.

Paste the Lines in the Output Column

Thus, you would see that you have found and replaced all the tab characters in Excel. For instance, the outcome should look like this.

Output with No Tab Characters

Read More: How to Indent Second Line in Excel Cell (5 Easy Ways)


How to Replace or Remove Tab Characters in Excel

You can also remove tab characters in Excel without finding them in cells manually. You can follow any of the ways below to remove or replace tab characters directly in Excel.

1. Combine TRIM, SUBSTITUTE & CHAR Functions to Replace Tab Character

The most common approach to substitute tab characters in Excel is to use the combination of the TRIM, SUBSTITUTE, and CHAR functions. Follow the steps below to use them properly in order to replace a tab character in Excel.

๐Ÿ“Œ Steps:

  • At the very beginning, click on the C5 cell and insert the following formula.
=TRIM(SUBSTITUTE(B5,CHAR(9),""))

Combine the SUBSTITUTION, TRIM, CHAR Functions to FInd and Replace Tab Character in Excel

  • Subsequently, hit the Enter button.
  • Afterward, place your cursor in the bottom right position of the C5 cell.
  • Consequently, a black fill handle will appear.
  • Following, drag it below to copy the formula for all the cells below.

Drag Fill Handle Below to Copy Same Formula

Thus, you will substitute all the tab characters with the null string successfully. And, the result would look like the following.

Output without Tab Characters


2. Use the CLEAN Function

Another easy approach to removing tab character in Excel is to use the CLEAN function. Go through the steps below to achieve your desired result with the CLEAN function.

๐Ÿ“Œ Steps:

  • Initially, click on the C5 cell.
  • Now, insert the following formula and press the Enter button.
=CLEAN(B5)

Use the CLEAN Function to Remove Tab Character in Excel

  • Afterward, place your cursor in the bottom right position of the C5 cell.
  • Subsequently, drag the fill handle downward upon its appearance.

Drag the Fill Handle Below

As a result, you can remove all the tab characters from cells in Excel. For example, the outcome would look like this.

Outputs without Tab Characters


3. Apply a VBA Code to Replace Tab Character in Excel

Besides, you can also apply a VBA code to replace a tab character in Excel. Follow the steps below to accomplish this.

๐Ÿ“Œ Steps:

  • First and foremost, go to the Developer tab >> Visual Basic tool.

Choose the Visual Basic Tool

  • At this time, the Microsoft Visual Basic for Applications window will appear.
  • Following, go to the Sheet4 from the VBAProject list.
  • Subsequently, write the following code in the appeared code window.
Sub RemoveTabCharacter()
    Selection.Replace Chr$(9), vbNullString
End Sub

Write Required Code to Find and Replace Tab Character in Excel

  • Now, close the Visual Basic window and go to the File tab from the main Excel ribbon.

Go to the File Tab

  • Following, choose the Save As option from the expanded File tab.

Choose the Save As option

  • As a result, the Excel Save As window will appear now.
  • Click on the Browse option.

Excel Save As Window

  • As a result, the Save As dialogue box will appear now.
  • Choose the .xlsm type from the Save as type: options.
  • Following, click on the Save button.

Save the Excel file in .xlsm Format to Enable Macro

  • Now, select the cells C5:C9 >> go to the Developer tab >> Macros tool.

Choose the Macro Option

  • As a result, the Macros window will appear.
  • Subsequently, choose Sheet4.RemoveTabCharacter macro and click on the Run button.

Run the Required Macro to Find and Replace Tab Character in Excel

As a result, all the tab characters will be replaced by a null string and the result would look like this.

Outputs without Tab Characters


4. Use Excel Power Query Tool

Moreover, you can use the Power Query tool to clean tab characters in Excel. Go through the following steps to do this.

๐Ÿ“Œ Steps:

  • At the very beginning, copy and paste the input lines into the Output column.
  • Subsequently, select the C5:C9 cells >> Data tab >> From Table/Range tool.

Access to the Power Query Window to Find and Replace Tab Character in Excel

  • As a result, the Power Query window will appear.
  • Following, right-click on the header >> choose the Transform option >> chose the Clean option.

Choose the Clean Option to Find and Replace Tab Character in Excel

  • As a result, you will see the tab characters are cleaned now.

Power Query Window without Tab Characters

  • Afterward, close the Power Query window.
  • Subsequently, the Power Query Editor window will appear. Click on the Keep button.

Choose the Keep Option

And, thus, you can see there is a new sheet containing your outputs without any tab character. The result would look like the following.

Output without Tab Characters

Read More: How to Remove Indent in Excel (4 Easy Methods)


Conclusion

In a nutshell, in this article, I have shown you 2 effective ways to find and replace tab character in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. Besides, you are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo