The following dataset has 5 comments about 5 people. There are unnecessary tab spaces.
Method 1 – Use the Excel CLEAN Function to Remove the Tab Space
Steps:
- Add a new column (Cleaned Data).
- Go to C4 and enter the following formula.
=CLEAN(B4)
- Drag down the Fill Handle.
Comments will be displayed without spaces.
Read More: How to Remove Space between Rows in Excel
Method 2 – Using the TRIM Function to Remove the Tab Space
The TRIM function removes all spaces from a text string except for single spaces between words.
The SUBSTITUTE function replaces existing text with a new text to a text string.
The CHAR function returns the character specified by the code number from the character set for your computer.
Steps:
- Go to C4. Enter the following formula.
=TRIM((SUBSTITUTE(B4,CHAR(9)," ")))
- Double click the Fill Handle.
This is the output.
Read More: How to Remove Space in Excel after Text
Method 3 – Using the Find and Replace Feature to Remove the Tab Space
Steps:
- Select all cells in the Comments column.
- In Editing, choose Find & Select. Click Replace.
We can also press Ctrl+H.
- In Find and Replace:
- Go to Find what and enter Alt+ 0010.
Note:
Your keyboard must have a separate number pad to type 0010 after the ALT key.
- Go to Replace with and enter spacebar.
- Click Replace All.
This is the output.
Read More: How to Remove Blank Spaces in Excel
Method 4 – Removing Tab Spaces Between Words with VBA
Steps:
- Go to Sheet Name.
- Choose View Code.
- In the Insert tab, choose Module.
- Enter the VBA code.
Sub RemoveTabSpace()
Selection.Replace Chr$(9), vbNullString
End Sub- Select the cells in the Cleaned Data column.
- In the VBA window, press F5 to run the code.
- This is the output.
All unnecessary spaces were removed.
Method 5 – Removing Extra Spaces with the Power Query
Steps:
- Select all cells in the Comments column.
- Go to the Data tab and choose From Table/Range.
- Check My table has headers.
- Click OK.
- The Power Query window will open.
- Go to the column heading.
- Right-click.
- Choose Transform.
- Click Clean.
- This is the output.
Read More: How to Remove Space after Number in Excel
Download Practice Workbook
Download this practice workbook to exercise.
Related Articles
- How to Remove Space Before Text in Excel
- Remove Leading Spaces in Excel
- How to Remove the Trailing Spaces in Excel
- How to Remove White Space in Excel
- How to Find and Replace Space in Excel
- How to Remove Extra Spaces in Excel
- Remove Space in Excel before Numbers
<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
