*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!

Please note that the TRIM function was designed to remove only the space character, which has value 32 in the 7-bit ASCII code system. 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 ASCII 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

To get rid of nonbreaking spaces (html character * *), which has value 160, use TRIM together with SUBSTITUTE and CHAR functions:

`=TRIM(SUBSTITUTE(A1, CHAR(160), " "))`

For full details, please see How to delete non-breaking spaces 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!

Tip. If you also want to remove spaces from the end of cells, make use of the Trim Spaces tool. There is no obvious Excel formula to remove leading and trailing spaces keeping multiple spaces between words intact.

### 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:

Note. The formula returns the count of **extra spaces** in a cell, i.e. leading, trailing, and more than one consecutive spaces between words, but it does not count single spaces in the middle of the text. If you want to get the total number of spaces in a cell, use this Substitute formula.

### 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 only removes 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

Does the idea of learning a handful of different formulas to deal with a trivial task sound ridiculous? Then you may like this one-click technique to get rid of spaces in Excel. Let me introduce you to Text Toolkit included in our Ultimate Suite. Among other things such as changing case, splitting text and clearing formatting, it offers the **Trim Spaces** option.

With Ultimate Suite installed in your Excel, 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 ( )

- Trim
- 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 are interested to try *Trim Spaces* in your sheets, you are welcome to download an evaluation version at the end of this post.

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!

## Available downloads

Trim Excel Spaces - formula examples (.xlsx file)

Ultimate Suite - trial version (.exe file)

## 114 comments

Hai Sir, can you assist me how trim functional can made text like this.

Actual expenses @ 666,842.08 is -31.46% lower compare budget 972,968.00.

Given data from MS Excell

Actual Budget Act vs Bgt (%)

666,842.08 972,968.00 -31.46%

For your information, I try use trim functional =trim(.... why the result become like this Actual expenses @ 666842.08 is -31.46% lower compare budget 972968

My question is, how can comma (,) can be display as per need.

Hi! For your information, I can't guess what formula you used because TRIM function can't delete commas, periods, and zeros. Specify which formula you mean, and describe the problem in more detail.

Thanks a lot for sharing the helpful and especially detailed information about "other whitespaces" :-)

=TRIM(CLEAN(A1))

3 methods to remove only leading spaces.

The first is yours.

The other two are mine:

=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))

=RIGHT(A4,LEN(A4)-FIND(RIGHT(TRIM(A4),LEN(A4)),A4)+1)

=MID(A6,FIND(LEFT(TRIM(A6),1),A6),LEN(A6))

The data in excel is just like that

17

18

19

How can i change it as

17

18

19

Plz sugguest the method for that

Hi! You can also find useful information in this article: How to remove all blank rows in Excel.

You can also get these values without empty cells in the other column by using the formula:

=TEXTSPLIT(TEXTJOIN(",",TRUE,A1:A10),,",")

The TEXTJOIN function merges values by ignoring empty cells, and the TEXTSPLIT function splits these values into rows.

Thanks a lot!! This substitute combination is amazing!! I'm so greatful!

Thanks.. u saved my time

How to convert date format from that..

2020-07-

21

in my excel file date shows this ways. I was downloaded data from business portal. please someone help me..

Hello!

Use substring functions to extract digits from the text string.

=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))

Use DATE function to set date.

Text to Date Tool can parse more than 500 combinations representing dates in text format and convert them to regular Excel dates. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Hi,

I have a problem:

First, I must remove the dash (/) with space ( ) and then keep only the first two words.

I have a function for dash to space: =SUBSTITUTE(A1,"/"," ")

and function for first two words: =TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",1000),2),1000)).

Can you help me to combine these two functions into one?

Thank you!

Hi!

Nest the first formula in the second formula

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"/"," ")," ",REPT(" ",1000),2),1000))

I hope my advice will help you solve your task.

Sorry, but space cleaning is not working for numeric details. Not removing spaces from beginning. Returns the exact same value.

Hi!

Unfortunately, I can't guess from what value and with what formula or tool you are removing spaces. However, I note that a number with a leading space is a number written as text. Maybe this article will be helpful: How to remove spaces in Excel - leading, trailing, non-breaking. If it's really a number, pay attention to the number format and cell format. Or explain your problem in more detail.

Hai, thank you for the formula. It's working.

like a pro! thanks

Please remove space in right

like "abcd@gmail.com "

at last 1 space is there I cant remove the space in right

Hi!

Have you tried the ways described in this blog post?

Formula works -

=TRIM(A1)

or

=MID(A1,1,LEN(A1)-1)

I have the same issue. I couldn't remove the space after the text.. example "ABC-123-DEF " the space after F how can I remove from one cell?

Hello!

I recommend reading this guide: How to remove spaces in Excel - leading, trailing, non-breaking. Pay attention to the first paragraph and the use of the TRIM function -

=TRIM(A1)

To remove space at the end of a text, you can replace it with an empty value using the SUBSTITUTE function -

=SUBSTITUTE(A1," ","")

If you want to remove only the last space at the end of the text, you can use the REPLACE function to remove the last space.

=REPLACE(A1,LEN(A1),1,"")

Thank you very much for this formula =MID(A1,1,LEN(A1)-1)

I use below formula in excel - may be helpful.

Chintan Jadeja (LEFT(A1,LEN(A1)-1)) Chintan Jadeja (14-Characters now - removed last space)

Thank you, it solved my problem.

"

Conoce los proyectos en venta de Inmobiliaria Bersa, una Empresa con más de 20 años de experiencia y más de 3.000 viviendas construidas en Ñuñoa, Las Condes, Lo Barnechea y Vitacura.

"

How to remove this. I have to double click on cell to see the data. Please help me

Hi!

I suppose this instruction will help: How to change and AutoFit column width in Excel.

Thank you !!

It was a useful practice exercise.

I usually prefer excel than google sheets but not this time :-)

how to remove space after the name in spreadsheet

Hi!

Please re-check the article above since it covers your case.

Your Excel blog posts are always incredibly helpful, and this is a great example. Thank you for saving my bacon for the hundredth time!

Good Morning,

Would anyone be able to help me remove a space from within a digital value. I would prefer to have a formula that would do this in a single cell. So if 8 228 is pasted in C2, I would like a formula that would replace the value in C2 to 8228.

Thanks in Advance.

Davie

Hi,

If a value is written in a cell, then you cannot also write a formula into it. Use a VBA macro for your task

Friends,

I wan to chop off characters from left of @ character and from " double quotes from the right of the word.

This is the sample which is in a column A1

sqlComm.Parameters.Add("@ADDRESS2", SqlDbType.VarChar).Value = txtadd2.Text;

sqlComm.Parameters.Add("@CITY", SqlDbType.VarChar).Value = drpCity.SelectedValue;

sqlComm.Parameters.Add("@PINCODE", SqlDbType.VarChar).Value = txtpincode.Text.Trim();

I wan to get

@ADDRESS2

@CITY

@PINCODE

as a result in another column B1

Please help.

Thanks

Ravi

Hello!

If I got you right, the formula below will help you with your task:

=MID(A1,SEARCH("@",A1,1), SEARCH("”",A1,1)-SEARCH("@",A1,1))

You can learn more about MID function in Excel in this article on our blog.

Thanks Alex, It really works - sorry for the late reply.

The reference link is amazing, and helped me a lot.

Thanks again.

Ravi

How i trim this format 01-JAN-20 07:00:00

Thank for this helpful blog. It saves my hours