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

There are several ways to remove spaces in Excel, and this tutorial will help you choose the right technique - delete leading and trailing spaces, eliminate extra spaces between words, get rid of non-breaking white space and non-printing characters. Continue reading →

Comments page 2. Total comments: 75

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

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

    1. Hello Richard!
      If I got you right, the formula below will help you with your task:

      =IFERROR(LEFT(A1,(FIND(" ",A1,1)-1)),A1)

      and

      =IFERROR(MID(A1,FIND(" ",A1)+1,256),"")

      We have a tool that can solve your task in a couple of clicks:Ablebits Data - Split Names.

      This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

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

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

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

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

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

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

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

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

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

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

    V,Good

    1. perfect

    2. Yes it worked. thanks

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

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

  15. Fabulous reference article! 'nuff said!

    Thanks!

  16. Thank you SO much!!!

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

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

    1. Reihana:
      Can you use COUNTIF or COUNTIFS instead of COUNTA?
      COUNTIF and COUNTIFS and SUMPRODUCT is more flexible than COUNTA.

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

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

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

  20. thanks for your precious post.

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

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

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

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

  25. Thanks for your sharing a good concept.

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

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

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

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

    1. Have you tried using regular expression in excel vba?

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