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.
How to Find and Replace Tab Character in Excel: 2 Ways
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.
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.
- Subsequently, select the C5 cell and right-click on your mouse.
- Subsequently, choose the Paste option from the context menu.
- Now, select the output cells (C5:C9) >> go to Home tab >> Editing group >> Find & Select tool >> Replace… option.
- 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.
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.
Note:
Here, you must type the 0009 number on the Numpad keyboard.
Read More: Find And Replace Multiple Values in Excel
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.
- Now, open any text document on your device.
- Afterward, right-click inside the text editor and choose the Paste option from the context menu.
- As a result, you will see that the inputs are inside the text editor now with tab characters.
- Now, select the tab character from any input >> right-click your mouse >> choose the Copy option from the context menu.
- Afterward, press Ctrl + H to open the Replace window.
- 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.
- Consequently, all the tab characters will be found and replaced by a space in the text editor.
- Now, select the lines from the text editor and right-click on your mouse.
- Following, choose the Copy option from the context menu.
- Afterward, go to the Excel file, and at the C5 cell right-click your mouse.
- Subsequently, choose the Paste option from the context menu.
Thus, you would see that you have found and replaced all the tab characters in Excel. For instance, the outcome should look like this.
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),""))
- 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.
Thus, you will substitute all the tab characters with the null string successfully. And, the result would look like the following.
Read More: How to Find and Replace Using Formula in Excel
2. Use the CLEAN Function
Another easy approach to removing tab characters 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)
- Afterward, place your cursor in the bottom right position of the C5 cell.
- Subsequently, drag the fill handle downward upon its appearance.
As a result, you can remove all the tab characters from cells in Excel. For example, the outcome would look like this.
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.
- At this time, the Microsoft Visual Basic for Applications window will appear.
- Following, go to Sheet4 from the VBAProject list.
- Subsequently, write the following code in the appeared code window.
Sub RemoveTabCharacter()
Selection.Replace Chr$(9), vbNullString
End Sub
- Now, close the Visual Basic window and go to the File tab from the main Excel ribbon.
- Following, choose the Save As option from the expanded File tab.
- As a result, the Excel Save As window will appear now.
- Click on the Browse option.
- 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.
- Now, select the cells C5:C9 >> go to the Developer tab >> Macros tool.
- As a result, the Macros window will appear.
- Subsequently, choose Sheet4.RemoveTabCharacter macro and click on the Run button.
As a result, all the tab characters will be replaced by a null string and the result would look like this.
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.
- As a result, the Power Query window will appear.
- Following, right-click on the header >> choose the Transform option >> chose the Clean option.
- As a result, you will see the tab characters are cleaned now.
- Afterward, close the Power Query window.
- Subsequently, the Power Query Editor window will appear. Click on the Keep button.
And, thus, you can see there is a new sheet containing your outputs without any tab character. The result would look like the following.
Download Practice Workbook
You can download our practice workbook from here for free!
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. Thank you!
Related Articles
- How to Find and Replace Values Using Wildcards in Excel
- 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
Get FREE Advanced Excel Exercises with Solutions!
