It is easy to import data into Excel from other sources such as MS Access, Oracle, or MySQL databases. However, many times the imported data is poorly formatted and difficult to work in Excel.
One cause of the poorly formatted data is the Tab symbols that display excessive unwanted spaces in the dataset. Sometimes these Tab symbols are invisible and another time they may be displayed as little squares.
In this tutorial, we will demonstrate how the following 4 easy methods can be used to remove excess Tab symbols from an Excel spreadsheet:
- Use the CLEAN function
- Use a combination of the SUBSTITUTE function and CHAR function
- Use the Excel VBA code
- Free SEO Plugin
We will use the following dataset that has invisible Tab spaces in Column B to show how these methods can be applied:
When you look at it, this dataset looks good but when you print preview the worksheet you will find out that it has unneeded extra Tab spaces in column B as follows:
It is these extra Tab spaces that usually make it hard to sort the data, return erroneous results in VLOOKUP, and otherwise make it difficult to work with and analyze the data.
Method 1 – Use the CLEAN function
The CLEAN function removes all nonprintable characters from a text. These nonprinting characters such as Tab symbols are usually found in files imported from other applications that will not print with your operating system.
We use the CLEAN function to remove the Tab spaces which are nonprinting characters from the dataset, by doing the following steps:
Step 1 – Select Cell D2 and key in the formula =CLEAN(B2) as follows:
Step 2 – Press the Enter key and use the Fill Handle to copy the formula down the column:
Step 3 – Go to File >> Print on the Excel Ribbon to Print Preview the dataset and confirm that the extra Tab spaces have been removed:
Step 4 – Select range D2:D7 and press Ctrl + C to copy the data without extra Tabs:
Step 5 – Select range B2:B7 and go to Paste >> Paste Special… to open the Paste Special dialog box.
Alternatively, you can open the Paste Special dialog box by pressing the keyboard shortcut of Ctrl + Alt + V.
Step 6 – In the Paste Special dialog box select Values and press OK:
The Paste Values will paste only the values in the range D2:D7 without the formulas.
The data with extra Tabs will be replaced by the one without Tabs:
Step 7 – Delete Column D for it is no longer needed.
Method 2 – Use a combination of the SUBSTITUTE function and CHAR function
The SUBSTITUTE function replaces existing text with new text in a text string. This function does not support wildcards and is case-sensitive.
The CHAR function returns the character specified by the code number from the character set of the computer. It returns a character when a valid character code is passed to it for example CHR(9) returns a Tab and can be used to add a Tab space to text in a formula.
By the use of the combination of these functions, we can remove excess Tab symbols from an Excel dataset. We achieve this by doing the following steps:
Step 1 – Select Cell D2 and key in the formula =SUBSTITUTE(B2, CHAR(9),””) as follows:
Step 2 – Press the Enter key and drag down the Fill Handle to copy the formula down the column:
Step 3 – Go to File >> Print to Print Preview the dataset and confirm that the extra Tab spaces have been removed:
Step 4 – Copy the data in range D2:D7 and paste it into range B2:B7 as Values.
Step 5 – Delete Column D for it is no longer needed.
Explanation of the formula
- In CHAR(9) the CHAR function returns the Tab character which is the character specified by the ASCII number 9 which has been passed to it.
- In SUBSTITUTE(B2, CHAR(9),””) the SUBSTITUTE function replaces the Tab character with an empty string (“”).
Method 3 – Use the Excel VBA Code
Using the Excel VBA code can make the work of removing excess Tab symbols from a large dataset easy and fast.
Do the following steps to remove the excess Tab symbols:
Step 1 – Press Alt + F11 to open the Visual Basic Editor. Alternatively, go to Developer >> Code >> Visual Basic.
Step 2 – In the Project Window right click your workbook and insert a new module by going to Insert >> Module:
Step 3 – In the new module key in the following code:
Sub RemoveTabsFromSelection() Selection.Replace Chr(9), vbNullString End Sub
Step 4 – Press Alt + F11 to switch back to the active worksheet. Alternatively, you can switch back to the active worksheet by clicking on the View Microsoft Excel button as shown below:
Step 5 – Select the range B2:B7 in the active worksheet.
Step 6 – Press the keyboard shortcut Alt + F8 to open the Macro dialog box. Alternatively, you can open the Macro dialog box by going to Developer >> Code >> Macros on the Excel Ribbon. Select the RemoveTabsFromSelection macro from the Macro dialog box and click Run:
Step 7 – Print Preview the results to confirm that all the excess Tabs have been removed:
Notice that in this method that the excess Tabs were removed from the range B2:B7 directly.
Explanation of the Code
- The replace method is applied on the range B2:B7 selection.
- The Chr function returns Tab which is the character which is specified by the code number 9.
- The Tabs in the selection are then replaced by empty strings as specified by the vbNullString constant.
Method 4 – Use the Free Excel SEO Add-in
This add-in offers two methods to remove tabs and other whitespace characters.
Look at this example:
There are a lot of tabs and spaces in front and at the end of the sentence. To remove them navigate to SEO >> Text >> Trim and Combine >> Trim Whitespaces.
After you click it, whitespace characters will be removed from the beginning and the end of the sentence.
Now we have to combine them into a single space between words. For this, we can use the second option from the dropdown: Combine Spaces.
This is the result:
Data from different databases can be easily imported into MS Excel. But many times these imported data come with nonprinting characters such as excessive Tab symbols.
If these excess Tab symbols are not removed, they will make the data difficult to work with for example sort and analyze.
In this tutorial, we have looked at three methods that can be used to remove the excess Tab symbols. The methods are applying the CLEAN function, using a combination of the SUBSTITUTE and CHAR functions, and using the Excel VBA Code.
If you prefer the quickest way, you can use the free Excel add-in.
You can use the method that best fits your situation.