How to remove spaces in Excel - leading, trailing, non-breaking

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. Leading, trailing and extra spaces between words

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.

How to remove blank spaces in Excel - leading, trailing, between words

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. TRIM formula to remove extra spaces in Excel

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).

How to delete line breaks and nonprinting characters

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 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)) Deleting line breaks and nonprinting characters

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.

How to remove non-breaking spaces 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: Removing non-breaking spaces

How to delete a specific non-printing character

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:

  1. 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 4th 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).

  2. 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: Delete a non-printing character based on its code value

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), " "))) Removing several non-printing characters and non-breaking spaces with a single formula

How to remove all spaces in Excel

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," ","") Remove all spaces in a cell

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

How to count spaces in Excel

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: Formulas to count spaces in cells

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

Formula-free way to remove spaces and clean data

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 with a formula. Of course, learning a handful of formulas is a good exercise to sharpen your skills, but it might be time-consuming.

Excel users who value their time and appreciate convenience can take advantage of the Trim Spaces included with our Ultimate Suite for Excel. One of these handy tools allows removing spaces and non-printing characters in a button click.

Once installed, Ultimate Suite adds several useful buttons to your Excel ribbon such as Trim Spaces, Remove Characters, Convert Text, Clear Formatting, and a few more. Special options to trim spaces and clean data in Excel

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

  1. Select the cells (range, entire column or row) where you want to delete extra spaces.
  2. Click the Trim Spaces button on the Ablebits Data tab.
  3. Choose one or several options:
    • Remove leading and trailing spaces
    • Trim extra spaces between words to one
    • Delete non-breaking spaces ( )
  4. Click the Trim button.

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

This is how you can quickly remove spaces in Excel cells. If you are curious to explore other capabilities, you are most welcome to download an evaluation version of the Ultimate Suite. I thank you for reading and look forward to seeing you on our blog next week!

Table of contents

75 comments

  1. I have a column of data where I would like to remove trailing spaces at the end of text. Some of my text includes two or more spaces within the text that needs to remain intact. So, the TRIM function will not work. There's also no consistency to the trailing spaces either. The text could have any number of trailing spaces.

    Here's a couple of examples:
    "212Q RML Moultrie GA " <-- this text includes 5 spaces between the "212Q" and "RML" which should not be adjusted. There is also 3 spaces at the end which should be eliminated
    "209Q RVI Vidalia GA " <-- this text includes 3 spaces between the "209Q" and "RVI" which should not be adjusted. There is also 10 spaces at the end which should be eliminated

    • Hello! You can use an Excel formula to remove all extra spaces to the right in a text string. The MID function splits the text string in individual chars. In this example, the string is 100 characters long. Then use the XMATCH function to find the position of the character greater than or equal to "!", starting from the last character. Let's explain that "!" is the first character that is greater than a space. Then we will use the LEFT function to extract the desired number of characters from the text string, not including the spaces at the end.

      =LEFT(A1,XMATCH("!",MID(A1,ROW(A1:A100),1),1,-1))

      You can also find useful information in this article: Remove leading spaces only (Left Trim).
      You can solve the problem without using formulas. We have a tool that can solve your task in a couple of clicks - Trim Spaces tool. It helps you eliminate unnecessary leading and trailing spaces. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

  2. ( 12121212) like this situation how to remove space in excel. Thanks

  3. A number has been copied from a table pasted on body of an email. When it is pasted in excel, formula bar reveals white space (more than 1 character long) at the beginning of the no. & at end also. How to eliminate such white space & make the no. a real no. which I can use in calculation?
    (My suggestion: a code which will delete all characters excluding nos. 0 to 9)

  4. Hello,

    I have an interesting issue which non of the above addresses. I have only one part number in a column which has three trailing spaces which TRIM, CLEAN, and SUBSTITUTE (including CHAR()) does not remove in formula. Here are the steps:
    PN = "E30392-I1-UR3-8-W   "
    1) original cell text from another worksheet (CALL IT SHEET2), referenced to active worksheet (CALL IT SHEET1) using formula
    2) when I use =CODE(RIGHT(SHEET2!A1,1)) = 87 which is ASCII code for "W", not unidentifiable space
    3) copy then paste as values from formula shows results with "E30392-I1-UR3-8-W" (no space), but I don't want to replace formulas with values
    4) tried with TRIM, CLEAN and SUBSTITUTE CHAR(160) to "" but to no avail
    5) I don't want to remove inside text spaces, only outside
    6) I cannot assume all values in column have same number of trailing spaces (it will chop off good characters in other text if I do)

    How to identify ASCII code if it does not see these spaces? How to remove outside spaces without losing inside spaces and good characters in cells?

    If I can upload file to you would be better.

      • Thank you Alexander for quick reply.

        My colleague figured out the ASCII code for mysterious spaces are 32. I attempted to use SUBSTITUTE CHAR(32) but still VLOOKUP did not recognize it in lookup table. I forgot to mention about the VLOOKUP. However, after deleting the mysterious spaces from source worksheet (SHEET2), VLOOKUP does find match in table.

        I don't have anymore info to give. Hopefully this helps you understand better my situation.

        • Hi! The TRIM function removes that space from the text. I don't know which VLOOKUP formula you are using. This formula works correctly:

          =VLOOKUP(TRIM(E1),A1:B10,2)

  5. these for any space before amount ,numbers, in excel sheet

    many thanks for formula =TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," "))))
    after that

    and you take the numbers copy and past in other place as new email then copy again from EMAIL to the main sheet by 123

    to enable us to use vlookup and sum ... easy

  6. How do I trim ONLY spaces at the end of the cell? There's a double-space at the end of the cell as well as one in the middle of the cell. I don't want to get rid of the one in the middle because subsequent formulas depend on it. How can I only trim the trailing spaces?

  7. Thank you very much, TRIM" function is extremely helpful

  8. Excellent!! Thank you very much guys,
    =TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," "))))

    solved my problem!!

    • This really helped to solve the problem i was facing in my daily work.

  9. hi
    i have to remove end space after number.. can you help me out this. i try trim and find replace but it's doesn't work.

    GrossAmount
    100800
    540006
    581276
    270003
    1139314
    1360295

    • Hi!
      The TRIM function removes leading and trailing spaces. Try to follow the instructions in this article. Also check other characters than spaces.

      • Very Helpful. Thank you!

  10. Very helpful, thanks!

  11. Great demonstration! You've made it into my bookmark folder!

  12. Best way to remove space to copy that space from cell and replace all with blank .

  13. Thank you for your article. It helped me to remove a non-breaking character using the CLEAN function after I had tried several other methods including the substitute. I am saving your article for future reference!

  14. Thanks a lot.
    Trim worked fine to remove spaces in between characters/ words. Great learn today.?

  15. What if you are trying to eliminate spaces in a a column of cell data that already have a formula applied to them.

    I cannot add a formula to fix this issue to cells that already have a formula in it correct?

    In my example I added a column to a table and added a formal to grab info from another column using the left/right functions.
    The problem is that after this formula grabs the results I end up with inconsistent spacing from cell to cell (really because I didn't use the best formula for my needs)

    When I try to use that data in a pivot table it does not lump the info together properly and I end up with "repeats " of data separated as unique items from each other.

    Any help would be appreciated thanks!

    • Hello!
      Without seeing your formula, it is difficult to give advice. I think you can change your formula so that it doesn't return extra spaces. A formula can be used as an argument for a TRIM function (something like TRIM(your formula)).
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

      • Hi thanks for the reply!
        I didn't get a notification in email saying there was a response to my question or I would have responded sooner.

        The formula in the column I have having issues with is the following:

        =RIGHT([@Description],LEN([@Description])-6)

        I am trying to return all characters starting at the first "y" (the number of y's will be variable as well.

        xxxxx yyyy
        xxxxxx yyyy

        The x's will not be greater than 6 characters 6 but they can be less

        When I want to filter for the yyyy in a pivot table it shows up twice because they are considered two unique pieces of data (one with an extra space), but to me they are the same.

        My data list has many example of this so I could have a slicer set up in my pivot table that has 50 items in the list due to the duplicates, when I really only should have 25 there.

        Im guessing I really should be using a Vlookup or index/match formula to get the results I want but I was trying to get it done a little easier where I don't have a lot of experience yet with other ways.

        Does this help explain it a little better?

        Thanks

          • Thanks a lot for your reply I will try this!

  16. Thank you so much for this. I had a data report that pumped out 221 spaces in 1 empty row between rows with data in them. I was able to use your helpful advise and create a formula based on your formula: =SUBSTITUTE(A2," ","")

    mine looks like this.
    =SUBSTITUTE(Data!A1," ","")

    THANK YOU ! THANK YOU!

  17. 58 57 53
    101 101 98
    75 75 67
    22 20 17
    56 53 47

    i tried all the techniques provided but still i can remove the spaces i fron and back of each numbers. for that i cant get the total sum as it counts only.

    thanks in advance

    • Hello!
      You don't need to remove spaces in front of numbers. Please check out this article to learn how to convert text to number with formula and other ways.
      If this is not what you wanted, please describe the problem in more detail.

  18. Thank You.

  19. the Formula "=TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," "))))" was very useful. my problem solved in a minutes. Thank you

    • why using 160 with CHAR

  20. Hi Svetlana,

    This article help me to solve my issue. I've use TRIM before but not aware about code(160) that can't removed neither by TRIM or CLEAN function but SUBTITUTE did it.

    Thank you again.

  21. Hi,
    I have a weird block inside of a cell. How do I delete it?

  22. Hello all!
    I have the below table in which I want to separate name and surname. I am able to do it using the the formula =LEFT(A1,(FIND(" ",A1,1)-1)) in column B and =MID(A1,FIND(" ",A1)+1,256) in column C. The problem arises when there is no surnmae. I want it to show just the name when there is no surname but it gives an error. How do I fix this

    Tom Bran
    Linda George
    Samanta Fisher
    Tommy Gill
    Sam Harrison
    Daniel
    Lucy Greenton

  23. ISSUE
    7,980
    20,590
    37,575
    25,985
    19,000
    10,420
    2,295
    275
    1,175
    3,490
    3,930
    1,175
    3,925

    SOLVE
    275
    1,175
    1,175
    2,295
    3,490
    3,925
    3,930
    7,980
    10,420
    19,000
    20,590
    25,985
    37,575

    Thank you so much. Trim function didn't work initially.
    I needed to use this one to clear out leading and trailing spaces TRIM alone didn't clear out.

    Thank you.

    =TRIM(SUBSTITUTE(A2, CHAR(160)," "))
    Regards,
    Ravi

  24. Thank you so much. Trim function didn't work initially.
    I needed to use this one to clear out leading and trailing spaces TRIM alone didn't clear out.

    Thank you.

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

    Sincerly
    Jorge

  25. How to remove spaces between two or more than words in excel. for example I want "TheNPLCitation" from "The NPL Citation"

  26. Very helpful !
    When I copied a table of numbers from a website, I had trouble converting the numbers stored as text into real numbers because of the "Non-Breaking Space" (NBS) character embedded in the data. By replacing "NBS" with the "normal space", it's way easier to work with the data. Thank god I find your article !

  27. The CODE function was helpful to find the offending character. Mine was 202. The SUBSTITUTE then worked like a charm.

  28. This was perfect and did exactly what I needed. you think you know Excel and then along comes another feature to learn! Thanks.

  29. This article saved my life!!! THANK YOU!!! I was struggling finding a way to clean up my google sheets, too many spaces in too many cells and over 5k rows to clean up. This was a godsend.

  30. TRIM did not work for me, but RichardP's idea to use LEFT (comment 11) worked perfectly. All the text strings I had instantly converted to the date and time formats I was looking for.

  31. Trim! Yes, that worked for me! Thanks so much!

  32. =TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," "))))

    V,Good

    • Yes it worked. thanks

    • perfect

  33. 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

  34. 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.

  35. Fabulous reference article! 'nuff said!

    Thanks!

  36. Thank you SO much!!!

  37. Stunning Formulas. looking forward to get more infor.

  38. 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.

  39. 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.

  40. thanks for your precious post.

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

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

  43. 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?

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

  45. Thanks for your sharing a good concept.

  46. 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.

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

  48. 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?

  49. 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!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)