Remove Unwanted Spaces From Text String



Many times we receive data in excel which is not properly formatted. It may contain some unwanted spaces which will waste our extra time to remove them.

In this blog post we will learn to remove these unwanted spaces from text string in excel. For this we will use TRIM function which is readily available in excel.

Where to find TRIM function on Excel Screen:

TRIM function can be found under ā€œText Functionā€ category under ā€œFormula Tabā€ as shown in below image:



Once we click on ā€œTextā€ category, we can see list of various Text Functions available in excel. TRIM function is highlighted in red in below image.


Once we click on TRIM option as highlighted above, we will get the function argument dialog box as shown in below image:



Syntax of TRIM Function:

The Syntax of TRIM function is as below:

=TRIM(text)



Arguments of TRIM Function:

To use the TRIM function we have to use only one arguments:

text: This is the only one and mandatory argument in which we can provide text string from which we want to remove unwanted spaces.

Example of TRIM Function:

Let’s learn about using this TRIM function with the help of simple example. We have prepared a dummy data as shown in below image. In this dummy data we have listed multiple text strings in column A and we will remove unwanted spaces from these text strings. The output will be displayed in Column B.


For this, select the cell in which we want to apply TRIM function. In this case we have selected cell ā€œB2ā€ where output of TRIM Function will be displayed. In cell ā€œB2ā€ enter TRIM formula as shown in below image.


Once we hit enter button, we can see the all the unwanted spaces in selected cell ā€œA2ā€ are removed.


To get the desired result in remaining cells as well from column B then we can just copy and paste the formula applied in cell B2. This will result in removal of all the unwanted spaces from other cells as well.



What TRIM Function Can Do?:

1. TRIM Function can remove unwanted spaces from beginning of the text string.

2. TRIM Function can remove unwanted spaces from the end of the text string.

3. TRIM function can remove unwanted spaces which are present in between two words (it will keep only once space between two words and will remove all other multiple spaces from two words).

Comments

Related Topics..

Fill Up, Down, Right & Left in Excel

Excel Shortcuts Ctrl A to Ctrl Z

How to Remove Duplicate Entries from Multiple Columns in Excel

Excel Pivot Table

Select all cells highlighted with specific color at a time

Title Bar in Excel

Merge data from Duplicate Rows into a Single Cell in Excel.

Insert Multiple Rows in Excel Based On Cell Value

Cell Address and Cell Range in Excel

Exploring Font Colors and Fill Colors in Excel