*The tutorial explains how to remove blank spaces in Excel using formulas and the Text Toolkit tool. You will learn how to delete leading and trailing spaces in a cell, eliminate extra spaces between words, get rid of non-breaking white space and non-printing characters.*

What's the biggest problem with spaces? They are often invisible to the human eye. An attentive user can occasionally catch a leading space hiding before the text or a few extra spaces between words. But there is no way to spot trailing spaces, those that keep out of sight at the end of cells.

It wouldn't be much of a problem if extra spaces were just lying around, but they mess up your formulas. The point is that two cells containing the same text with and without spaces, even if it's as little as a single space character, are deemed different values. So, you may be racking your brain trying to figure out why an obviously correct formula cannot match two seemingly identical entries.

Now that you are fully aware of the problem, it's time to work out a solution. There are several ways to remove spaces from string, and this tutorial will help you choose the technique best suited for your particular task and the data type you are working with.

If your data set contains superfluous spaces, the Excel TRIM function can help you delete them all in one go - leading, trailing and multiple in-between spaces, except for a single space character between words.

A regular TRIM formula is as simple as this:

`=TRIM(A2)`

Where A2 is the cell you want to delete spaces from.

As shown in the following screenshot, the Excel TRIM formula successfully eliminated all spaces before and after the text as well as consecutive spaces in the middle of a string.

And now, you only need to replace values in the original column with trimmed values. The easiest way to do this is using *Paste Special* > *Values*, the detailed instructions can be found here: How to copy values in Excel.

Additionally, you can use the Excel TRIM function to remove **leading spaces only**, keeping all spaces in the middle of a text string intact. The formula example is here:

How to trim leading spaces in Excel (Left Trim)

When you import data from external sources, it's not only extra spaces that come along, but also various non-printing characters like carriage return, line feed, vertical or horizontal tab, etc.

The TRIM function can get rid of white spaces, but it cannot eliminate non-printing characters. Technically, Excel TRIM is designed to only delete value 32 in the 7-bit ASCII system, which is the space character.

To remove spaces and non-printing characters in a string, use TRIM in combination with the CLEAN function. As its names suggests, CLEAN is purposed for cleaning data, and it can delete any and all of the first 32 non-printing characters in the in the 7-bit ASCII set (values 0 through 31) including **line break **(value 10).

Assuming the data to be cleaned is in cell A2, the formula is as follows:

`=TRIM(CLEAN(A2))`

If the Trim/Clean formula joins the contents of multiple lines without spaces, you can fix it by using one of these techniques:

- Utilize the "Replace All" feature of Excel: in the "Find what" box, input a carriage return by pressing the Ctrl+J shortcut; and in the "Replace with" box, type a space. Clicking the
*Replace All*button will swap all line breaks in the selected range for spaces. - Use the following formula to substitute Carriage Return (value 13) and Line Feed (value 10) characters with spaces:
`=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13)," "), CHAR(10), " ")`

For more information, please see How to remove carriage returns (line breaks) in Excel.

If after using the TRIM & CLEAN formula some stubborn spaces are still there, most likely you copy/pasted the data from somewhere and a few non-breaking spaces sneaked in.

To get rid of nonbreaking spaces (html character * *), replace them with regular spaces, and then have the TRIM function remove them:

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

To better understand the logic, let's break down the formula:

- A non-breaking character has value 160 in the 7-bit ASCII system, so you can define it by using the CHAR(160) formula.
- The SUBSTITUTE function is used to turn non-breaking spaces into regular spaces.
- And finally, you embed the SUBSTITUTE statement into the TRIM function to remove the converted spaces.

If your worksheet also contains non-printing characters, use the CLEAN function together with TRIM and SUBSTITUTE to get rid of spaces and unwanted symbols in one fell swoop:

`=TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," "))))`

The following screenshot demonstrates the difference:

If the liaison of 3 functions discussed in the above example (TRIM, CLEAN and SUBSTITUTE) was not able to eliminate spaces or non-printing characters in your sheet, it means those characters have ASCII values other than 0 to 32 (non-printing characters) or 160 (non-breaking space).

In this case, use the CODE function to identify the character value, and then employ SUBSTITUTE to replace it with a regular space and TRIM to remove the space.

Assuming the spaces or other undesirable characters that you want to get rid of reside in cell A2, you write 2 formulas:

- In cell B2, detect the problematic character value by using one of the following CODE functions:
- Leading space or non-printing character at the beginning of the string:
`=CODE(LEFT(A2,1))`

- Trailing space or non-printing character at the end of the string:
`=CODE(RIGHT(A2,1))`

- Space or non-printing character in the middle of the string, where
*n*is the position of the problematic character:`=CODE(MID(A2,`

*n*, 1)))

In this example, we have some unknown non-printing character in the middle of the text, in the 4

^{th}position, and we find out its value with this formula:`=CODE(MID(A2,4,1))`

The CODE function returns value 127 (please see the screenshot below).

- Leading space or non-printing character at the beginning of the string:
- In cell C2, you replace CHAR(127) with a regular space (" "), and then trim that space:
`=TRIM(SUBSTITUTE(A2, CHAR(127), " "))`

The result should look something similar to this:

If your data contains a few different non-printing chars as well as non-breaking spaces, you can nest two or more SUBSTITUTE functions to remove all unwanted character codes at a time:

`=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(127), " "), CHAR(160), " ")))`

In some situations, you may want to remove absolutely all white spaces in a cell, including single spaces between words or numbers. For example, when you have imported a numeric column where spaces are used as thousands separators, which makes it easier to read big numbers, but prevents your formulas from calculating.

To delete all spaces in one go, use SUBSTITUTE as explained in the previous example, with the only difference that you replace the space character returned by CHAR(32) with nothing (""):

`=SUBSTITUTE(A2, CHAR(32), "")`

Or, you can simply type the space (" ") in the formula, like this:

`=SUBSTITUTE(A2," ","")`

After that, replace formulas with values and your numbers will calculate properly.

Before removing spaces from a certain cell, you may be curious to know how many of them are actually there.

To get the total count of spaces in a cell, do the following:

- Calculate the entire string length using the LEN function: LEN(A2)
- Substitute all spaces with nothing: SUBSTITUTE(A2," ","")
- Compute the length of the string without spaces: LEN(SUBSTITUTE(A2," ",""))
- Subtract the "space-free" string length from the total length.

Assuming the original text string is in cell A2, the complete formula goes as follows:

`=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))`

To find out how many **extra spaces** are in the cell, get the text length without extra spaces, and then subtract it from the total string length:

`=LEN(A2)-LEN(TRIM(A2))`

The following screenshot demonstrates both formulas in action:

Now that you know how many spaces each cell contains, you can safely delete extra spaces using the TRIM formula.

As you already know, many extra spaces and other unwelcome characters can lurk unnoticed in your sheets, especially if you import your data from external sources. You also know how to delete spaces in Excel by using an appropriate formula. Of course, learning a handful of formulas is a good exercise to sharpen your Excel skills, but it might be time-consuming.

Those Excel users who value their time and appreciate convenience can take advantage of our Text Toolkit for Excel, a time saving-tool that allows removing spaces and non-printing characters in a button click.

Once installed, the Text Toolkit adds several useful buttons to your Excel ribbon such as *Trim Spaces*, *Remove Characters*, *Convert Text*, *Clear Formatting*, and a few more.

And now, whenever you want to remove blank spaces in your Excel sheets, perform these 4 quick steps:

- Select the cells (range, entire column or row) where you want to delete extra spaces.
- Click the
*Trim Spaces*button on the ribbon. - Choose one or more options:
- Remove
**leading**and**trailing**spaces - Trim
**extra****spaces between words**to one - Delete
**non-breaking**spaces ( )

- Remove
- Click the
*Trim*

Done! All extra spaces are deleted in a single click.

If you are curious to explore other capabilities of this handy add-in, you are most welcome to download a 15-day evaluation version. If you like the tool and decide to get a license of the Text Toolkit or the entire Ultimate Suite for Excel, we are happy to offer you the 15% coupon code: AB14-BlogSpo.

This is how you can quickly remove spaces in Excel cells. I thank you for reading and look forward to seeing you on our blog next week!

## 22 Responses to "How to remove spaces in Excel - leading, trailing, non-breaking"

Hello Allo,

Thanks for posting about "TRIM" function. However I do have an excel having values with leading spaces. When used both Trim and Value(Trim) functions its not working. The leading space is still exists.

May I request some one to help...

Thanks!

ShriAkhilBansal

MrAmitBhatnagar

MrAbhayLodha

ShriAbhayGupta

AbhileshBabel

ArunBharatRam

VinnieMehta

I want to space in the All names. Please tell me formula n any path.

ShriAkhilBansal

e.g Shri Akhil Bansal

Have you tried using regular expression in excel vba?

Thanks Svetlana - my bugbear was the nbsp space which I didnt realise...yours was only article which addressed it. Many thanks indeed!

Table of Contents

Asia-Pacific

1 Fermented Plant Extract Overview

1.1 Product Overview and Scope of Fermented Plant Extract

=> I wanted to remove space between 'Table of Contents' & 'Asia-Pacific' in all row in excel sheet. can u pls sugg me the formula.

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

Thanks for your sharing a good concept.

Thanks a lot for sharing the various possibilities in Trim function. It helped me to understand in detail.

Hi..

Thanks for detailed explanation.

I have data in a column and wouldn't like to use a helper column.

In this case how could remove spaces in a column in one go?

This was very helpful. The step by step suggestion actually resolved my challenges. Thank you.

This is an outstanding resource! Thanks so much for your help, Svetlana!

thanks for your precious post.

Get the space character in another cell using LEFT(cell,1) or whatever gets the trailing or leading character.

Copy the cell and paste into the replace dialog (CTRL H) "Find what" box. Leave the "Replace with" blank then change all.

Hello, So I have a COUNTA formula running, but it counts a cell even if people accidentally press a space. Is there a way to prevent it from counting those spaces?

Reihana:

Can you use COUNTIF or COUNTIFS instead of COUNTA?

COUNTIF and COUNTIFS and SUMPRODUCT is more flexible than COUNTA.

Hey Doug, thanks for responding.

Unfortunately, I don't have a criteria to use for the COUNTIF.

Basically, we are scanning serial number and there is no single criteria. I would have to use multiple criteria and I'm sure it'll slow down the document especially since I'm going to use it on 365 with multiple users.

Use the COUNTA (range) - (COUNTA (range)-COUNTIF(Range," ")) which will count the cells that have just a space.

Stunning Formulas. looking forward to get more infor.

Thank you SO much!!!

Fabulous reference article! 'nuff said!

Thanks!

Extremely helpful!! Thanks, I needed the Code step to figure out the HTML character that was being copied in with my data. All the steps were great!

Many Thanks.

Hello.

I have a range of cells (in Excel 2011) in columns with text. Some cells start with a space before the text.

I need to color (conditional formatting) the cells that start with 1 space (just 1, if there are more than 1 spaces before the text nothing should happen).

Tried lot of formulas, didn't succeed to find the right one.

Any help will be appreciated.

Thanks in advance.

Saul