Comments on: Excel TRIM function - quick way to remove extra spaces

The tutorial demonstrates a few quick and easy ways to trim spaces in Excel – leading, trailing, and excess spaces between words. Continue reading →

Comments page 2. Total comments: 126

  1. Thank you !!

  2. It was a useful practice exercise.
    I usually prefer excel than google sheets but not this time :-)

  3. how to remove space after the name in spreadsheet

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

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

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

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

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

      1. Thanks Alex, It really works - sorry for the late reply.
        The reference link is amazing, and helped me a lot.
        Thanks again.
        Ravi

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

  8. Thank for this helpful blog. It saves my hours

  9. Hi i have tried trim and substitute and dint remove spaces can you help on this

    ‭1410560268‬
    ‭1410562663‬
    ‭1410563841‬
    ‭2150165024‬

    1. Hello!
      Please specify what formula you used and what problem or error occurred. Reread the article above, it covers your case completely.

  10. Your are great person and resolved my problem, very very much thank you ,please keep it up your such great work. I got removed extra space in my excel data from you article.

  11. THANK YOU

  12. I had a situation where the spaces appeared to be a different character that wasn't a space.
    I then copied the character, pasted it in de Find box of the Find and Replace function, with nothing in the Replace box and then pressed replace all.
    That finally helped.

  13. Thank you so much for this, it was exactly what I needed and clearly worked through my issues. Amazing work!

  14. Supeerrrr!
    Thank you! :)

  15. Brilliant advise and very clear explanations. Thank you!

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

  17. i have space before and after datetime,How do i remove leading and trailing spaces for below datetime
    " 01/20/20120 03:20 "

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

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

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

  19. How to subtract the enter space (Alt + enter) from the select cell?

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

  21. thank you!

  22. Thank you for the TRIM(Substitute function.

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

  24. How can we remove . Or , in column..
    Example ry.gmail.com., rh.gmail.com,,

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

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

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

  28. 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%) ? ?

  29. thanks - the =TRIM(SUBSTITUTE(A1, CHAR(160), " ")) did the trick, glad I kept reading :)

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

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

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

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

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

  35. Hi,

    May i know how to trim full sheet.

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

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

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

  37. How do I remove gaps in this below example?

    3 4 1 2 9 9 9

    I would like to do this as 3412999.

  38. Hi there,
    How can I remove only the trailing spaces but retain the leading spaces.
    Thank you
    Monica

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

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

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

      1. Like-wise to add up space in the right side of detail, left side of detail, and the middle side of detail, what is the short key to add space.

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

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

  42. 14448.46
    154962.72
    33519.10
    13693.68

    please give formula to remove space before numbers.

  43. Thank you - finding the code for the space and replacing worked like a charm!

  44. Thank you! Worked perfect for my needs.

  45. how to remove double camma , , ,

  46. Please remove space in right
    like 1Z5582310495718193
    at last 1 space is there I cant remove the space in right

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

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

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

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

  50. Please help me to to convert 15-196 to 151-96

    1. Hello,

      Please try the following formula:

      =REPLACE(SUBSTITUTE("15-196","-",""),4,0,"-")

      Hope it will help you.

      1. SUPPER!!

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