Perhaps you just got a file from a coworker with a bunch of text data in a Word format. You need to import these to Excel. But being written in Word, there may be a bunch of random Spaces in the data. Now you are thinking you have to manually delete all the spaces from all these numbers and texts. This is where the TRIM function comes to the rescue.
In this guide, we will show you what the TRIM function is, what it does, and how you can use it effectively while working with data that you got from a coworker. We will also discuss some facts about the function before wrapping up.
So let’s dive into what the TRIM function is and how you can use it.
Contents
What Is The TRIM Function
The TRIM function is a built-in Function in Excel. Although it is a lesser-used feature, it is a pretty handy function. The TRIM function falls under Excel’s TEXT functions.
The TRIM function removes extra spaces from your data and keeps your worksheet looking clean. Often, data imported from other applications have inconsistent spacings in between them which may cause visual disturbances and simply be annoying.
The TRIM function removes such inconsistencies and annoying extra spaces in your dataset to give you a clean output while working in Excel. The TRIM function can also be used with a bunch of other functions as we showed in this guide to get more effective use out of the TRIM function.
Syntax
The basic syntax for the TRIM function is:
TRIM(text)
The text argument is mandatory as it is the only argument taken. The TRIM function takes any text as an argument from which you may want to remove extra spaces.
Now, what do we mean by extra spaces? The TRIM function removes spaces in the text except for the single spaces in between words. So maybe you have two spaces in between two words, TRIM will remove one space and keep only one.
How To Use TRIM
In this guide we will show you a total of 7 methods of using TRIM to remove extra spaces from your dataset. These methods gives you an overall efficient use of TRIM. with these methods you can remove annoying spaces and characters from your inconsistent dataset easily.
So, let’s start.
To Trim Spaces Manually From A Sentence
The very basic use of TRIM is to remove spaces form a Sentence. For example, to use TRIM, we have to write the following formula:
=TRIM(“I Love Working With EXCEL: XYOLogic”)
See all these unnecessary spaces between the words? In Microsoft Word, this may be done for aesthetic purposes. But in Excel, these spaces are often unnecessary.
So the TRIM function takes the text string and removes the extra spaces and returns only:
I Love Working With EXCEL: XYOLogic
As you can see, Excel only kept single spaces in between words and trimmed all the extra random spaces from the sentence. If you can’t see your full sentence you can just adjust the column width to show the full range of words.
Removing Spaces from Paragraphs
You can also use TRIM to remove spaces from a paragraph. For example, if you have line breaks between your sentences or you have a paragraph, TRIM will remove any line breaks and paras and will give you an output of a single line with the extra spaces removed.
For example, if you write this formula in Excel:
=TRIM(“I
Love
Working With
EXCEL: XYOLogic”)
We will get the following output from Excel:
Similarly, you can remove Spaces from longer paragraphs or sentences. But in that case, TRIM will give you an error saying “You have entered too many arguments for this function”.
Let’s say you want to remove the spaces from this Lorem Ipsum sample paragraph with the following TRIM formula.
=TRIM(“Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras nec fermentum arcu, in semper dui. Cras suscipit et turpis non efficitur.
Vestibulum vehicula augue in dui laoreet, vel fringilla nibh fringilla. Mauris a felis arcu. Fusce sagittis dapibus commodo.
Nulla eget dictum orci. Cras fringilla vehicula urna in rutrum.”)
This will look like this in Excel:
As you can see Excel returned an error saying “You have entered too many arguments for this function”. This is because the TRIM function cannot take many arguments. For these use cases, you can put the text in a cell and then use TRIM to remove spaces from that cell.
For example, we will put the same paragraph in Cell B1. Now, to TRIM the spaces, in cell B2 we will write this formula:
=TRIM(B1)
As you can see this will provide you with the same text but removed extra spaces.
Trimming Spaces From A Range Of Cells
As you can see TRIM can also be used with cell references. This means that like other functions like SUM, CHOOSE, the TRIM function can also work with a range of cell references.
For example, we have a list of names here with irregular spaces that we copied from a word document.
If we want to remove the extra spaces from the range of cells, we can write the following formula in the B2 cell:
=TRIM(A2:A11)
This will remove the extra spaces from the cell range A2:A11. This is what we get if we do this in Excel.
As you can see, Excel did not put all the cell’s values in one cell, rather it only trimmed the first text string from the adjacent cell. This is a smart feature of Excel. To get all the values from the A2:A11 cell range, we can just use the autofill handler.
Just hover on the bottom right of the B2 cell and copy the formula to the subsequent cells.
As you can see, this fills the cells after removing the incorrect spacing.
Removing Leading Spaces
Now you know how to remove extra spaces from your list of names. But what if you had a list of names but you had it formatted to look a certain way and only want to remove the leading spaces in this list. You can also achieve this by using Left TRIM.
For example, we have the same data, but with some numbers and some leading spaces.
Now we want to remove the leading spaces from this list, but keep the spaces in the middle of the words as is. To do this we have to use a formula that may look intimidating. But don’t fret. The formula is quite easy.
In our case, the formula is:
=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))
Looking at the formula, this uses the FIND function to find the first text character in your data. In this case 111. Then the formula gives this string to MID function that returns the text strings starting from 111. The LEN function calculates the total lenth of the text string.
This returns an output like this:
As you can see, the formula only removed the leading spaces from the data and kept all the extra spaces in between the words and the numbers as is. This might be especially helpful if you are trying to keep a specific formatting style in your database.
TRIM With Numbers
Up until this point we have worked with only texts to show you how TRIM would work. But, you can use TRIM to remove spaces from numbers as well.
For example, say we have the following datasheet imported from a Microsoft Word file containing some numbers.
As you can see, the numbers are quite jumbled up as there are a lot of spaces in between the numbers. To fix this, we can use the TRIM function like before and write this formula in the B2 cell
=TRIM(A2:A11)
This will provide us with the following results:
But this poses a problem as TRIM cannot work with numbers. As you can see in the next image, the function returns a series of text strings as the data type is Text.
This dataset can also show as General for you. But still would be a Text value.
We can fix this by using the values of the text strings to get our desired numbers. To do this, we can use the VALUE function.
Just change the formula to:
=VALUE(TRIM(A2:A11))
This will return the value of the cells as numbers.
As you can see in the image, the TRIM function now returned a number value for the cells without the unnecessary spaces.
TRIM with SUBSTITUTE
We can also use the TRIM function with the SUBSTITUTE function to add text strings from a range of cells and show them together by removing extra cell spaces. Confused? Let me explain.
Say you have a datasheet like this:
As you can see there are a bunch of cells containing a bunch of texts. Now you want to add each of the cells into a single cell, but you want to remove the empty spaces generated from the empty cells in between.
You can do this with the SUBSTITUTE and TRIM function together. To do this you just write the following formula:
=SUBSTITUTE(TRIM(A3&” “&B3&” “&C3&” “&D3),” “,”, “)
Looking at this formula, the SUBSTITUTE function adds all the texts from the cells A3, B3, C3, and D3 and separates them with commas in between.
As you can see, the formula returns the text values from each of the cells as the TRIM function removes the spaces generated from the empty cells.
The SUBSTITUTE function adds the data of the cells and replaces each space (“ “) with a comma followed by a space (“, “). This along with TRIM returns the added strings with commas in between.
But if you want to merge text values from cells into a single sentence using SUBSTITUTE and TRIM, you can just replace the comma followed by a space (“, “) with only a space (“ “). This will make the formula look like this:
=SUBSTITUTE(TRIM(A3&” “&B3&” “&C3&” “&D3),” “,” “)
You will get the following result if you use this formula.
Pretty nifty! Right? Now you can merge all those scattered names and numbers together.
TRIM with CLEAN
If you have a dataset that contains Nonprinting characters that come in the shape of boxes, you may use TRIM with the CLEAN function. This will remove all the weird characters and give you clean text with the spaces removed.
Nonprinting characters include tabs, line breaks, Page breaks, null characters among others. Nonprinting characters like line breaks are pretty common if you are copying a paragraph from a word document. Now if you have a dataset with Nonprinting characters like line breaks, you can just use the CLEAN function with TRIM.
For example, in our case we wrote this formula:
=TRIM(CLEAN(A2:A11))
As you can see in the image, the CLEAN function with TRIM removed the Nonprinting values and returned us with a clean text value.
This will save you from dealing with all the weird box-shaped characters manually and give you clean excel data.
3 Things to Know About TRIM
There are 3 things you should know about the TRIM function if you want to use the function properly. These are:
1. The TRIM function removes extra spaces from your texts which may have inconsistent spacing. This means that the TRIM function will only keep one single space between words. Any extra spaces other than one single space between words will be removed.
This means if you provide TRIM with a paragraph, the function will remove the paras, and provide you with one single line of text with single spacing between the words.
2. If you want to get into technicalities, the TRIM function basically trims the 7-bit ASCII spaces from text. But in the Unicode character set, there is another space character with a decimal value of 160. In Unicode, this space character is known as a nonbreaking space character.
On the Web, the character is widely used as  – an HTML entity. The TRIM function keeps this space while removing others.
3. If you are putting direct Texts inside the TRIM function without using cell references, you have to put inverted commas before and after the texts. Otherwise, you will get a #NAME! Error.
This is because, without inverted commas, Excel will not recognize strings as texts.
Wrapping Up
In this guide we have talked about what the TRIM function is, what it does, and how you can use it. Although the TRIM function has been around for ages, it is a lesser-known function. Many people will just manually fix extra spaces in their work as they don’t know the function.
But using TRIM, you can easily remove the extra spaces from your datasheet. TRIM is a very handy feature especially while dealing with data generated by third-party applications.
If you have randomly scattered data imported from a Word document you can use the TRIM function to do the hard labor of removing unnecessary spaces.
We hope that our guide will help you achieve mastery over the TRIM function. So, go ahead and let Excel do the hard work for you.
Similar Post: