Best way to remove all tabs from an Excel worksheet?

I have a worksheet that contains multiple cells that include a bullet point followed by a tab and then text. I want to remove all of those tabs between the bullet points and the text. What did I do and try until now to make it work:

1. Used Search & Replace: Problem is, it seems like in contrast to a .txt or .csv file I can't copy in the tab (ALT+0009) into the search field of Excel there. For .txt and .csv files it works without any problems doing it that way.

2. Looked out for formulas and found SUBSTITUTE, wrote the following formula:

   SUBSTITUTE(A1, CHAR(9),"")

That does the trick for one column. But since I have a large amount of columns and cells I need to apply that for, it would be pretty cumbersome. Is there another way or maybe a best practice on how to do that? Thank you for any help!

Solution 1:

Welcome to SuperUser! Several websites I found say to use the Excel Find/Replace window and type ALT+0009 in the Find box. This method doesn't work in Excel 2020 (and I suspect other versions too).

What works for me is a one line VBA script that removes all tab characters from the selected cells.

1. Select the cells containing tab characters to remove.

2. Press ALT+F11 to open the VBE (Visual Basic Editor).

3. Paste this code into a code module, make sure the cursor is on the code and press F5 to run it.

Sub RemoveTabsFromSelection()

Selection.Replace Chr$(9), vbNullString

End Sub

This code replaces every tab character Chr$(9) in Selection with nothing vbNullString which effectively deletes them.