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

We have 5 cells that have the tab character inside. We want to find and replace these tab characters.

Method 1 – Using the Find and Replace Dialogue Box

Steps:

• Create a new column named Output to get your result.
• Select the inputs (B5:B9) and right-click.
• Choose the option Copy from the context menu.

• Select the C5 cell and right-click.
• Choose the Paste option from the context menu.

• Select the output cells (C5:C9) and go to Home tab and the Editing group.
• Choose the Find & Select tool and the Replace… option.

• The Find and Replace dialogue box will appear.
• Go to the Replace tab and type Alt + 0009 in the Find what: text box.
• Put a space in the Replace with: text box.
• Click on the Replace All button.

• You will see that all the tab characters are found and replaced with a spacebar. For our example, the output should look like this.

Read More: Find And Replace Multiple Values in Excel

Method 2 – Utilizing a Text Editor to Find and Replace the Tab Character

Steps:

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

• Open a new text document on your PC.
• Right-click inside the text editor and choose the Paste option from the context menu.

• You will see that the inputs are inside the text editor now with tab characters.

• Select the tab character from any input, right-click, and choose the Copy option from the context menu.

• Press Ctrl + H to open the Replace window.

• Paste the selection inside the Find what: text box.
• Put a space in the Replace with: text box.
• Click on the Replace All button.

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

• Select all the lines from the text editor and right-click.
• Choose the Copy option from the context menu.

• Go to the Excel file and right-click on cell C5.
• Choose the Paste option from the context menu.

• This pastes the new values without the tab character.

How to Replace or Remove Tab Characters in Excel

Method 1 – Combine TRIM, SUBSTITUTE, and CHAR Functions to Replace the Tab Character

Steps:

• Click on C5 and insert the following formula:

• Hit the Enter button.
• Place your cursor in the bottom right position of the C5 cell. A black fill handle will appear.
• Drag it down to copy the formula for all the cells.

• This will replace the tab character with a space in all cells.

Read More: How to Find and Replace Using Formula in Excel

Method 2 – Use the CLEAN Function

Steps:

• Click on C5.
• Insert the following formula and press Enter.

• Place your cursor in the bottom right corner of the C5 cell.
• Drag the fill handle down.

• This fills in the column with the formula.

Method 3 – Apply VBA Code to Replace the Tab Character in Excel

Steps:

• Go to the Developer tab and choose the Visual Basic tool.

• The Microsoft Visual Basic for Applications window will appear.
• Go to Sheet4 from the VBAProject list.
• Copy the following code in the code window.

Sub RemoveTabCharacter()
    Selection.Replace Chr$(9), vbNullString
End Sub

• Close the Visual Basic window and go to the File tab from the main Excel ribbon.

• Choose the Save As option.

• Click on the Browse option.

• Choose the .xlsm type from the Save as type: options.
• Click on the Save button.

• Select the cells C5:C9 and go to the Developer tab, then select Macros.

• The Macros window will appear. Choose the Sheet4.RemoveTabCharacter macro and click on the Run button.

• All the tab characters will be replaced by a null string, and the result should look like this.

Method 4 – Use Excel Power Query

Steps:

• Copy and paste the input lines into the Output column.
• Select the C5:C9 cells.
• Go to the Data tab and select From Table/Range.

• The Power Query window will appear. Right-click on the header and choose the Transform option.
• Select the Clean option.

• You will see the tab characters are cleaned now.

• Close the Power Query window. The Power Query Editor window will appear.
• Click on the Keep button.

• You can see there is a new sheet containing your outputs without any tab character. The result should look like the following image:

Download the Practice Workbook

Related Articles

• How to Find and Replace Asterisk (*) Character in Excel
• [Fixed!] Excel Find and Replace Not Working
• How to Find and Replace in Excel Column
• How to Replace Special Characters in Excel
• How to Substitute Multiple Characters in Excel

<< Go Back to Find and Replace | Learn Excel