Remove all tab characters(\t) in excel cells

Question

Can I remove all tab characters(\t) in excel using 'Find And Replace'?

I can't use VBA or Excel Function, because sheet has many cells and don't know which cell has tab char.

I just want remove all tab characters(\t) in all cells in sheet.

EDIT)

Here my data.

SARSPED " WKZLR42" WKZ7F68

WKZLR42 cell has tab characters front it.

Answer 1

This to me is also always such an annoying issue; the way it used to work was that one could use ALT Codes in the Find & Replace functionality to find these special characters. For a horizontal tab this would be ALT + 0009 on the keypad.

Note that there is a distinctive difference between ALT codes with or without leading zero's as per this table. It appears that all the non-printable and non-displayable control characters (displayed in red in given link) can not be used within the office environment. I even think this is a Windows-wide issue as I tried to alter the hotkeys in the Register-Editor and the key-combination is not recognized.

Other tooling, like Notepad++, does seem to pick up the ALT code. So since you can't use VBA, you can't use formulae and you can't use the Find & Replace method; the two things that I could think of are:

---

1) Notepad++

Save your Excel as a CSV file and open in Notepad++. Within this application, hit Ctrl+H. Make sure to replace as per following screenshot:

Save your CSV and now load this back into your Excel.

---

2) PowerQuery

The 2nd option I can think of, depending on how your data is set up, is to load your data in PowerQuery. Within PQ select all your columns and right click on of the headers > 'Replace Values' > 'Advanced Options' > 'Replace using special characters' > 'Tab' > 'OK'.

Load back the data to Excel.