How to identify horizontal tabs in Excel and replace it

Question

I have thousands of data in my excel file, they are scraped data from various websites, initially stored in CSV files then migrated into Excel. This data is for use of my web server.

Now my problem is that there are horizontal tabs hidden somewhere among many spaces, and they are causing a critical problem for my web server.

---

I'll give an example to clarify here:

Data in Excel: "This is a cell"

Data when encoded to be used as url parameter: "This%20is%09a%20cell"

You can clearly see that one of the spaces in between has been encoded to %09, which represents a horizontal tab. This is causing a severe problem from my server and I NEED TO identify and replace this.

So, how would I go about finding a horizontal tab in Excel?

Answer 1

Burgi's answer does the trick, but rather than having to duplicate the entire dataset with a worksheet full of formulas, you can achieve the same via a few lines of script.

Sub ReplaceTabs()
   Dim c As Range

   For Each c In ActiveCell.CurrentRegion
      c.Value = Replace(c.Value2, Chr(9), Chr(32))
   Next c
End Sub

There are a few methods to step through all the relevant cells (e.g. you could just target specific columns with ActiveSheet.Range("A:A") etc., or select the cells in question and use Selection.Cells) but this method would work if the cells are contiguous and the tabs might be in any cell.

It's not super speedy (200,000 cells took about 10s on my machine) but should be quick enough.

If this is a common task then you could build this into an import script.

Answer 2

This code should work for you:

=SUBSTITUTE(A1,CHAR(9),CHAR(32))

The above formula replaces all instances of CHAR(9) with CHAR(32) in the given string.

• CHAR(9) - The tab character
• CHAR(32) - The space character

See this wiki article for the lookup table that Windows uses.

Answer 3

to speed up Lunatik's answer to turn off/on updating while the macro is working.

Dim tmp As Boolean
tmp = Application.ScreenUpdating
' this stores current value
Application.ScreenUpdating = False

   Dim c As Range

   For Each c In ActiveCell.CurrentRegion
      c.Value = Replace(c.Value2, Chr(9), Chr(32))
   Next c

Application.ScreenUpdating = tmp