Excel TRIM function - quick way to remove extra spaces
The tutorial demonstrates a few quick and easy ways to trim Excel spaces. Learn how to remove leading, trailing, and extra spaces between words, why Excel TRIM function is not working and how to fix it.
Are you comparing two columns for duplicates that you know are there, but your formulas cannot find a single duplicate entry? Or, are you adding up two columns of numbers, but keep getting only zeros? And why on earth does your obviously correct Vlookup formula return just a bunch of N/A errors? These are only a few examples of problems that you may be seeking answers to. And all are caused by extra spaces hiding before, after or between numeric and text values in your cells.
Microsoft Excel offers a few different ways to remove spaces and clean up your data. In this tutorial, we will investigate the capabilities of the TRIM function as the fastest and easiest way to delete spaces in Excel.
TRIM function - remove extra spaces in Excel
You use the TRIM function in Excel removes extra spaces from text. It deletes all leading, trailing and in-between spaces except for a single space character between words.
The syntax of the TRIM function is the easiest one could imagine:
Where text is a cell that you want to remove excess spaces from.
For example, to remove spaces in cell A1, you use this formula:
=TRIM(A1)
And the following screenshot shows the result:
Yep, it's that simple!
If in addition to extra spaces, your data contains line breaks and non-printing characters, use the TRIM function in combination with CLEAN to delete the first 32 non-printing characters in the in the 7-bit ASCII code system.
For example, to remove spaces, line breaks and other unwanted characters from cell A1, use this formula:
=TRIM(CLEAN(A1))
For more information, please see How to remove non-printing characters in Excel
How to use TRIM function in Excel - formula examples
Now that you know the basics, let's discuss a few specific uses of TRIM in Excel, pitfalls that you may face and working solutions.
How to trim spaces in an entire column of data
Supposing you have a column of names that have some whitespace before and after the text, as well as more than one spaces between the words. So, how do you remove all leading, trailing and excess in-between spaces in all cells at a time? By copying an Excel TRIM formula across the column, and then replacing formulas with their values. The detailed steps follow below.
- Write a TRIM formula for the topmost cell, A2 in our example:
=TRIM(A2) - Position the cursor to the lower right corner of the formula cell (B2 in this example), and as soon as the cursor turns into the plus sign, double-click it to copy the formula down the column, up to the last cell with data. As the result, you will have 2 columns - original names with spaces and formula-driven trimmed names.
- Finally, replace the values in the original column with the trimmed data. But be careful! Simply copying the trimmed column over the original column would destroy your formulas. To prevent this from happening, you need to copy only values, not formulas. Here's how:
- Select all cells with Trim formulas (B2:B8 in this example), and press Ctrl+C to copy them.
- Select all cells with the original data (A2:A8), and press Ctrl+Alt+V, then V. It is the paste values shortcut that applies the Paste Special > Values
- Press the Enter key. Done!
How to remove leading spaces in a numeric column
As you have just seen, the Excel TRIM function removed all extra spaces from a column of text data without a hitch. But what if your data is numbers, not text?
At first sight, it may seem that the TRIM function has done its job. Upon a closer look, however, you will notice that the trimmed values do not behave like numbers. Here are just a few indications of abnormality:
- Both the original column with leading spaces and trimmed numbers are left-aligned even if you apply the Number format to the cells, while normal numbers are right-aligned by default.
- When two or more cells with trimmed numbers are selected, Excel displays only COUNT in the status bar. For numbers, it should also display SUM and AVERAGE.
- A SUM formula applied to the trimmed cells returns zero.
From all appearances, the trimmed values are text strings, while we want numbers. To fix this, you can multiply the trimmed values by 1 (to multiply all the values in one fell swoop, use the Paste Special > Multiply option).
A more elegant solution is enclosing the TRIM function in VALUE, like this:
=VALUE(TRIM(A2))
The above formula removes all leading and trailing spaces, if any, and turns the resulting value into a number, as shown in the screenshot below:
How to remove only leading spaces in Excel (Left Trim)
In some situations, you may type duplicated and even triplicated spaces between words to make your data better readable. However, you do want to get rid of leading spaces, like this:
As you already know, the TRIM function eliminates extra spaces in the middle of text strings, which is not what we want. To keep all in-between spaces intact, we'll be using a bit more complex formula:
=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))
In the above formula, the combination of FIND, MID and TRIM calculates the position of the first text character in a string. And then, you supply that number to another MID function so that it returns the entire text string (the string length is calculated by LEN) starting at the position of the first text character.
The following screenshot shows that all leading spaces are gone, while multiple spaces between words are still there:
As a finishing touch, replace the original text with the trimmed values, as shown in step 3 of the Trim formula example, and you are good to go!
How to count extra spaces in a cell
Sometimes, before removing spaces in your Excel sheet, you may want to know how many excess spaces are actually there.
To get the number of extra spaces in a cell, find out the total text length using the LEN function, then calculate the string length without extra spaces, and subtract the latter from the former:
=LEN(A2)-LEN(TRIM(A2))
The following screenshot shows the above formula in action:
How to highlight cells with excess spaces
When working with sensitive or important information, you may be hesitant to delete anything without seeing what exactly you are deleting. In this case, you can highlight cells containing extra spaces first, and then safely eliminate those spaces.
For this, create a conditional formatting rule with the following formula:
=LEN($A2)>LEN(TRIM($A2))
Where A2 is the topmost cell with data that you want to highlight.
The formula instructs Excel to highlight cells in which the total string length is greater than the length of the trimmed text.
To create a conditional formatting rule, select all the cells (rows) that you want to highlight without column headers, go to the Home tab > Styles group, and click Conditional formatting > New Rule > Use a formula to determine which cells to format.
If you are not familiar with Excel conditional formatting yet, you will find the detailed steps here: How to create a conditional formatting rule based on formula.
As demonstrated in the screenshot below, the result perfectly corroborates with the extra spaces count that we got in the previous example:
As you see, the use of the TRIM function in Excel is easy and straightforward. Nevertheless, if someone wants to have a closer look at the formulas discussed in this tutorial, you are welcome to download the Trim Excel Spaces Workbook.
Excel TRIM not working
The TRIM function is designed to remove only the space character, represented by code value 32 in the 7-bit ASCII character set. In the Unicode character set, there is one more space character called the non-breaking space, which is commonly used on web pages as the html character . The nonbreaking space has a decimal value of 160, and the TRIM function cannot remove it by itself.
So, if your data set contains one or more white spaces that the TRIM function does not remove, use the SUBSTITUTE function to convert non-breaking spaces into regular spaces and then trim them. Assuming the text is in A1, the formula goes as follows:
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))
As an extra precaution, you can embed the CLEAN function to clean the cell of any non-printable characters:
=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))
The following screenshot shows the difference:
If the above formulas do not work for you either, chances are that your data contain some specific nonprinting characters with code values other than 32 and 160. In this case, use one of the following formulas to find out the character code, where A1 is a problematic cell:
Leading space: =CODE(LEFT(A1,1))
Trailing space: =CODE(RIGHT(A1,1))
In-between space (where n is the position of the problematic character in the text string):
=CODE(MID(A1, n, 1)))
And then, supply the returned character code to the TRIM(SUBSTITUTE()) formula discussed above.
For example, if the CODE function returns 9, which is the Horizontal Tab character, you use the following formula to remove it:
=TRIM(SUBSTITUTE(A1, CHAR(9), " "))
Trim Spaces for Excel - remove extra spaces in a click
If you'd rather not waste time learning a handful of different formulas to deal with a trivial task, you may like this one-click technique to get rid of spaces in Excel. Let me introduce you to Text Toolkit for Excel. Among other things such as changing case, splitting text and clearing formatting, it offers the Trim Spaces option.
With this add-in installed, removing spaces in Excel is as simple as this:
- Select the cell(s) where you want to delete spaces.
- Click the Trim Spaces button on the ribbon.
- Choose one or all of the following options:
- Trim leading and trailing spaces
- Trim extra spaces between words, except for a single space
- Trim non-breaking spaces ( )
- Click Trim.
That's all there is to it! All extra spaces are removed in a blink.
In this example, we are only removing leading and trailing spaces, keeping multiple spaces between words intact for better readability - the task that Excel formulas cannot cope with is accomplished with a mouse click!
If you want to try this handy Excel Trim Spaces tool in your sheets, a 30-day evaluation version can be downloaded here. If you like it and decide to get a license of the Text Toolkit or Ultimate Suite for Excel that includes this as well as 60+ other time-saving tools, be sure to grab the 15% coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.
I thank you for reading and look forward to seeing you next week. In our next tutorial, we will discuss other ways to trim spaces in Excel, please stay tuned!
You may also be interested in:
65 Responses to "Excel TRIM function - quick way to remove extra spaces"
Hi, I have tried all the method above but still cannot remove the leading spaces. When I used LEN to see how many spaces were there, it showed zero.
I have attached few data from excel below for your reference. Many thanks.
350.00
500.00
7,867.56-
6,533.32-
=TRIM(CLEAN(SUBSTITUTE(*cell*,CHAR(160), " ")))
try with this
This works!
Used it to remove a single trailing space in a number column
What a fantastic Solution given..... Thanks a lot
WHY WE WAS USE CHAR(160),AND LIKE THIS WE HAVE SOME MANY OPTIONS LIKE CHAR(1),CHAR(30),CHAR(144)
yes its working thanks alot
Thank you! This worked for me!
It helped
very good page.......
Dear sir,
How to trim reduce for - & +
Dear sir,
How to trim reduce for - & +
Example -710
-11151
+5141
-5156
Hi, can I check how do I trim the figures as such to derive the section that I would like to have? eg, 344MY/4.15510.7201/AABBCCDDEEEFF - (100) SSS. I only need 4.15510.7201 in the next column.
Thanks.
Hello,
if your task is to extract the text from between two slashes, you can use this formula:
=MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)-FIND("/",A1)-1)
A1 is the cell with your value.
Hope it helps!
I have a large number of cells each consisting of multiple lines of text ending in a line break. Many cells have a space immediately preceding the line break and I want to include those particular spaces (but no others). I've tried a Find/Replace using a space followed by Control J as the Find criterion but this didn't work. Any suggestions would be appreciated.
This is a fantastic blog post. Thank you so much for helping me with an ongoing problem I've had in excel! I now understand the function better and will be bookmarking this article should I encounter any problems in the future! Thank you so much for writing a clear and concise article - very helpful indeed!
Best Wishes!
-Barb
Hi,
when I used below formaula, I can see the leading spaces got removed but trailing spaces still exist.Please suggest to remove trailing spaces also.
=MID(B2,FIND(MID(TRIM(B2),1,1),B2),LEN(B2))
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Please help me to to convert 15-196 to 151-96
Hello,
Please try the following formula:
=REPLACE(SUBSTITUTE("15-196","-",""),4,0,"-")
Hope it will help you.
SUPPER!!
Hi, please can someone help?
i am trying to remove space between a chapter number and the header in excel.
E.g.
1.0 Space rocket
I have tried the Trim function with no success.
Hello,
If I understand your task correctly, please try the following formula:
=LEFT(A1,FIND(" ",A1)-1) & TRIM(MID(A1,FIND(" ",A1),LEN(A1)))
Hope this will help.
Hi, I have a formula like this "=cell("address",index($B$8:$C$103,MATCH(B9,$B$8:$B$103,0),2))". It gives a result is "$C$9".
How can I remove the "$" symbol so that will give the result such as "C9"? I want a formula that combines with the above formula.
Greetings, does anyone know how to remove spaces in between a numeric text? I've tried Find & Replace, TRIM, and SUBSTITUTE formulas with no success. Following is the data I am working with:
-90 000
-123 750
-132 020
-58 038
-135 000
Please remove space in right
like 1Z5582310495718193
at last 1 space is there I cant remove the space in right
how to remove double camma , , ,
Thank you! Worked perfect for my needs.
Thank you - finding the code for the space and replacing worked like a charm!
14448.46
154962.72
33519.10
13693.68
please give formula to remove space before numbers.
Raj:
You should be able to use the TRIM() function. However when I try to use it with your sample data, it won't work. By the time I get your sample in an email I can't really tell what you're working with.
How many spaces are in front of the numbers?
Are they spaces or another character.
Hi, how do I remove the black space behind a series of numbers
for example:123,456.00 , there was an invisible space after the two 0.
I have used find and replace, trim, substitude, it all didn't work.
Hope you can help.
How can I remove space(s) at the end of cell range as these are not visible in cells but problematic when referred to value anywhere else?
Aslam:
You should be able to use the TRIM function.
It removes leading and trailing spaces.
It's just TRIM(A1) where the data to trim is in A1.
Hi there,
How can I remove only the trailing spaces but retain the leading spaces.
Thank you
Monica
Monica:
I don't believe there is a function in Excel to remove only leading or only trailing spaces. VBA has the LTRIM and RTRIM functions which will either remove all the leading spaces or trailing spaces.If you are comfortable with VBA here is a macro you can use.
Sub TrimTrailingSpaces()
' 1. Select the cells that hold the trailing spaces you want trimmed
' 2. run the macro
' 3. Substitute LTrim for RTrim to remove only leading spaces
Dim rngCel As Range
For Each rngCel In Selection.Cells
rngCel.Value = RTrim(rngCel.Value)
Next
End Sub
How do I remove gaps in this below example?
3 4 1 2 9 9 9
I would like to do this as 3412999.
Hello, Kazi,
Thank you for contacting us.
If we understand your task correctly, please have a look at our Remove Characters add-in. It can help you remove extra spaces from your cells. The add-in is included in out Text Toolkit for Excel that contains 8 useful add-ins to manage text data in Excel (remove extra spaces, substrings and non-printable characters, change case, split cells, etc.).
You can install a fully functional 7-day trial version of Text Toolkit and see if it works as you need. Here is the direct download link.
Feel free to contact us again if you have any questions or need further assistance.
I copy a lot of information from excel into other programs.... whenever I do it seems like there are quite often extra spaces that have to be removed when the information is pasted into the other program from excel. How do I keep these extra spaces from happening?
Hello, Misti,
Thank you for your question.
Please have a look at our Trim Spaces add-in for Microsoft Excel. It can help you get rid of all the extra spaces at a glance. The add-in is available as a part of our Text Toolkit that contains 8 useful add-ins to manage text data in Excel (remove spaces, substrings and non-printable characters, change case, split cells, etc.).
Feel free to install a fully functional 7-day trial version of Text Toolkit and see if it works for you using this direct link.
Pleas let us know if there is anything else we can help you with.
Hi,
May i know how to trim full sheet.
Hi Arpit,
We have a ready-made solution for your task. You can get rid of all the extra spaces in your worksheet in a few clicks using our Trim Spaces add-in. It is available as a part of our Text Toolkit that contains 8 tools to manage text data in Excel (remove extra spaces, substrings and non-printable characters, change case, split cells, etc.).
Feel free to install a fully functional 7-day trial version of Text Toolkit and check if Trim Spaces and other add-ins work for you. Here is the direct download link.
Please contact us if you have any questions or need further assistance.
THANK YOU!!! The trim function wasn't working and it was driving me crazy until I saw the substitute =TRIM(SUBSTITUTE(A1, CHAR(160), " ")) which worked perfectly!
i'm analyzing monthly data, so in that one of the column is having short description from that i want to extract particular text value and insert into other seperate column.
Example:
from B column particular text value(database name) to new column
..learned a lot reading this..! However, it didn't solve my problem. I imported data from Oracle into Excel and a key datafield has invisible spaces or character codes, 1 leading & 1 trailing, Code CHAR(63). I used the LEN and CODE functions to determine where the extra characters were and which one was/it the problem. But, even using Trim-Substitute after learning of this from your article, my issue remains..! Here is the formula I used:
=TRIM(CLEAN(SUBSTITUTE(X38906, CHAR(63)," ")))
where the X3896 is the cell in question.
I have been up all night with this; any ideas for a sleepy man..?
Was unable to remove the spaces using the substitute command with space and char(160), I used the CODE command and found that the code used for the space and resolved the issue.
Many Thanks.
Hello
I have a cellphone data plan i wanted to know how i can calculate mbs to remove the seven digits or so on right attached is an example
0.33510685
12.47925282
3.751661301
16.2502079
20.00243378
15.0015173
0.526992798
1.656933785
12.81834698
thanks
thanks - the =TRIM(SUBSTITUTE(A1, CHAR(160), " ")) did the trick, glad I kept reading :)
Hi, I would like to ask how can i get total number by removing the percentage.
Sample:
Name Combined Grade Percentage
Robert 7 (18.92%) 7 18.92%
Andrea 100 (0.00%) ? ?
Carlo 10 (13.51%) ? ?
Seth 1 (2.70%) ? ?
Carlos 100 (2.70%) ? ?
The text below has leading spaces which when deleted manually, excel will sum. However, when I use trim it does not sum the values. I have tried using the code formulae and it gives me 160 and also substituted the same in my formulae but still not working. Please assist, tahnk you.
19/04/2019 703.0
19/04/2019 15944.0
19/04/2019 55.0
19/04/2019 1672.0
19/04/2019 65668.0
19/04/2019 9000.0
19/04/2019 3627.0
I am a total excel rookie :(, how do I use the TRIM function to remove zeros which prefix a subsequent 4-5 digit number within a column ?
Eg, I have 000991061 / 000992641 / 000992676.....and I only need 991061 / 992641 / 992676
Sorry !!!! - thank you for any help you can offer !!!
Hi! I'm trying the formula to highlight trailing spaces:
=LEN($A2)>LEN(TRIM($A2))
This looks like it should be straightforward, but Excel is throwing an error (not enough arguments for this function).
Any suggestions why this is not working?
How can we remove . Or , in column..
Example ry.gmail.com., rh.gmail.com,,
Hi
How to remove last few characters ONLY for certain cells in a column (e.g. there are 2000 data in a column, for 150 cells I want to trim last 5 characters - i.e. I dont want. How to remove that. One more condition, column is not arranaged in ascending or decending)
Thank you for the TRIM(Substitute function.
thank you!
Hi! i have the following issue with a stream of numbers that have been exported from an accounting program(can not get a different version exported) with both . and ,
the numbers look like this and i can find a way to eliminate either the . or the comma. do you know how to?
2.450,00
1.404,11
12.577,03
3.634,93
thanks
How to subtract the enter space (Alt + enter) from the select cell?
Please use the following formula:
=SUBSTITUTE(A1,CHAR(13),"")
We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Tools - Convert Text and Remove Characters.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
How do you remove extra spaces from the example below?
Example (in one cell):
- IT
- Marketing
- HR
- Finance
Desired result to show in one cell:
- IT
- Marketing
- HR
- Finance
Hello Pramod!
Please try to use the following formula:
=SUBSTITUTE(SUBSTITUTE (A32,CONCATENATE(CHAR(10),CHAR(10)),CHAR(10)), CONCATENATE(CHAR(13),CHAR(13)),CHAR(13))
It will replace two consecutive line breaks with one. As the result, the text in your cell will look like you need, without extra empty lines.
I hope it’ll be helpful.
i have space before and after datetime,How do i remove leading and trailing spaces for below datetime
" 01/20/20120 03:20 "
Hello!
I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.
Sir, What a fantastic site. Downloaded a report in excel from a site and was unable to find something in the internet to help replace the non-breaking space at the beginning of the numbers that have been converted to text. Saved my today and the many more to come! Glad to learn about non-breaking space as well. Thank you so much.
Brilliant advise and very clear explanations. Thank you!
Supeerrrr!
Thank you! :)