TRIM Function in Excel – Shiksha Online

TRIM Function in Excel – Shiksha Online

6 mins read1.6K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Jan 10, 2024 10:37 IST

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. 

2022_03_TRIM-function-in-Excel-1.jpg

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

Content

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)
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more
How to use the TRIM formula in Excel

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.

Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.
Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and efficient...read more

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
COUNT Function in Excel – Shiksha Online
COUNT Function in Excel – Shiksha Online
Read on the article to understand how COUNT, COUNTA, and COUNTIF functions work in MS Excel.
NOW Function in Excel – Shiksha Online
NOW Function in Excel – Shiksha Online
The article covers the NOW function in MS Excel. It is in continuation to the MS Excel basic functions series.
MEDIAN Function in MS Excel
MEDIAN Function in MS Excel
The article covers the MEDIAN function in MS Excel. It is in continuation to the MS Excel basic functions series.

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

FAQs - TRIM Function in Excel

Is the TRIM function case-sensitive in Excel?

No, the TRIM function is not case-sensitive. It removes spaces regardless of whether they are upper or lowercase.

Can I use the TRIM function within a formula or function in Excel?

Yes, you can use the TRIM function within other Excel formulas or functions. For example, you can use it to clean up text data before performing calculations or comparisons.

What happens if I apply the TRIM function to a cell without extra space?

Applying the TRIM function to a cell without extra spaces will return the original text string unchanged.

Can I use the TRIM function to remove non-breaking spaces in Excel?

The TRIM function does not remove non-breaking spaces (ASCII character 160). To remove non-breaking spaces, you can use SUBSTITUTE or a custom formula.

Is there an alternative to the TRIM function for removing spaces in Excel?

Yes, you can also use the SUBSTITUTE function or a combination of other text functions like SUBSTITUTE, LEFT, RIGHT, and LEN to remove spaces or perform more complex text manipulations.

Is there a limit to the length of text strings that the TRIM function can handle in Excel?

The TRIM function in Excel can handle text strings of up to 32,767 characters in length, which is the maximum limit for a cell's content.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments