TRIM Function in Excel – Shiksha Online

If you are dealing with datasets from Excel, you know that you have to deal with characters that you’d rather have removed, such as unnecessary spaces. This happens especially if you are sourcing or copying the data from somewhere. So, instead of removing them manually, use the TRIM function. Continuing our Basic MS Excel functions tutorials, we have covered the TRIM function in Excel in this article. 

So let’s see how to TRIM spaces in Excel.

Content

• What is the TRIM function?
• TRIM Syntax
• How to use the TRIM formula in Excel
• Things to Remember
• How to use String Functions – Right, Mid, and Left

What is the TRIM function?

TRIM is a straightforward and basic Microsoft Excel function. It is used in removing extra spaces from a specific text string or a cell containing text, leaving only a space between words. In short, it removes the leading and trailing spaces from the text. It also cleans and removes any non-printable characters from the data.

TRIM Syntax

The formula for TRIM in MS Excel is 

=TRIM( text )

Arguments – Text

Returns

The TRIM function returns a string/text value.

Type of Function

• Worksheet function (WS)
• Visual Basic for Applications (VBA)

To help you understand how to TRIM in Excel, we will explore the two main methods of applying it in a new formula using sample data.

If you’re building a formula with TRIM, it only has one argument: the text. It can be the text you enter directly into the formula or a cell reference to a cell that contains your text. Now that we theoretically know how to use Excel's TRIM function let’s move on to some practical parts.

Example 1 

Suppose a cell in Excel contains a text string like this:

I have a cat named Larry

This same string contains a single space between each word. However, if there were extra spaces in the text string before the first word.

For example, ” I have  a cat named Larry”, or if there were multiple spaces between words like “I have a   cat named  Larry. “, you can use the TRIM function to remove them for you.

The TRIM formula uses TRIM to remove unnecessary spaces from cell C5. To use this formula yourself, replace the cell reference “C5” with your own.

=TRIM(C5)

Press Enter. Now you have the clean data from cell C5, without any spaces.

Example 2

Now let’s see how it works with the numeric data –

Example 3

Insert a new column beside the Column where you want to clean the data. Give the same heading to the new column.

Now tupe=TRIM and choose the first cell with data, here A2.

Press Enter, and now you have the cleaned data (A2) in cell B2. You can either click on the cell end (encircled in red) to fill in the entire column or drag and drop to the column of your choice.

We will press Enter and copy the values from Column B

Now we will Paste the data in the original column, Column A here, in the form of Values.

Now delete Column B. You have the cleaned data.

Things to Remember

• While you can do this task manually, TRIM allows you to quickly remove unnecessary spaces from large data sets, saving you time in the process
• TRIM removes the ASCII space character (decimal value 32) from text strings, which is the standard space character used in most text
• The exception is for text on web pages, where the Unicode non-breaking space character (decimal value 160) is often used for spaces. TRIM will not remove it, so if this character exists in your text, you will need to remove it manually

How to use String Functions – Right, Mid, and Left

Now that you know how to use TRIM in Excel, let’s move on to some other interesting string functions. If you must pull the first digits or the last alphabets from your data sets, you can use the ‘LEFT’, ‘MID’, and ‘RIGHT’  functions in Excel. ‘LEFT’, ‘MID’, and ‘RIGHT’ functions help parse out substrings from a single data string. This is dependent on the relative position of the substring in the given single data string.

Let’s understand this by an example – 

Example – 4

Let’s sort the numerals and alphabets from the data below.

LEFT Function 

Under subheading Left, type=Left(cell no, numbers of digits to be parsed)

We want to extract 5 digits from the source string. 

We will use the formula below-

=LEFT(C3,5)

Press Enter

RIGHT Function 

We saw how to apply the LEFT trim function in excel. Similarly, we will extract the alphabets from the string using the RIGHT function.

We will use the below formula

=RIGHT(C3,5)

MID Function 

Now coming to the MID function. It has the below syntax – 

=MID (text, start_num, num_chars)

Arguments 

text: The text to parse from

start_num: Positioning of the first character to extract

num_chars: Number of characters to extract

Here,

C4: Cell Number

5: The positioning of the test we want to extract is “a.” 

3: Total number of characters we want to extract, which are abc=3 characters

Thank you for reading this post, I hope now you know how to use the TRIM command in Excel.

Do try using these functions on your dataset. With this, I will now move on to the intermediate functions in Excel in my next blog.

Keep reading.

Top Trending Articles:

Data Analyst Interview Questions | Data Science Interview Questions | Machine Learning Applications | Big Data vs Machine Learning | Data Scientist vs Data Analyst | How to Become a Data Analyst | Data Science vs. Big Data vs. Data Analytics | What is Data Science | What is a Data Scientist | What is Data Analyst