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 3. Total comments: 128

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

  2. thank you!

  3. Thank you for the TRIM(Substitute function.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    3 4 1 2 9 9 9

    I would like to do this as 3412999.

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

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

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

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

  23. 14448.46
    154962.72
    33519.10
    13693.68

    please give formula to remove space before numbers.

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

  25. Thank you! Worked perfect for my needs.

  26. how to remove double camma , , ,

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

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

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

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

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

  32. Hi,
    when I used below formaula, I can see the leading spaces got removed but trailing spaces still exist.Please suggest to remove trailing spaces also.

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

    1. Hello,

      I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

      However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  33. This is a fantastic blog post. Thank you so much for helping me with an ongoing problem I've had in excel! I now understand the function better and will be bookmarking this article should I encounter any problems in the future! Thank you so much for writing a clear and concise article - very helpful indeed!

    Best Wishes!
    -Barb

  34. I have a large number of cells each consisting of multiple lines of text ending in a line break. Many cells have a space immediately preceding the line break and I want to include those particular spaces (but no others). I've tried a Find/Replace using a space followed by Control J as the Find criterion but this didn't work. Any suggestions would be appreciated.

  35. Hi, can I check how do I trim the figures as such to derive the section that I would like to have? eg, 344MY/4.15510.7201/AABBCCDDEEEFF - (100) SSS. I only need 4.15510.7201 in the next column.
    Thanks.

    1. Hello,

      if your task is to extract the text from between two slashes, you can use this formula:
      =MID(A1, FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)-FIND("/",A1)-1)

      A1 is the cell with your value.
      Hope it helps!

  36. Dear sir,

    How to trim reduce for - & +

    Example -710
    -11151
    +5141
    -5156

    1. use absolute function (abs)

  37. Dear sir,

    How to trim reduce for - & +

  38. very good page.......

  39. Hi, I have tried all the method above but still cannot remove the leading spaces. When I used LEN to see how many spaces were there, it showed zero.
    I have attached few data from excel below for your reference. Many thanks.
    350.00
    500.00
    7,867.56-
    6,533.32-

    1. Working. Thank you so much.

    2. It helped

    3. =TRIM(CLEAN(SUBSTITUTE(*cell*,CHAR(160), " ")))

      try with this

      1. This works for me. Thank you a lot. I will like to further learn from you anonymous

      2. Thanks Boss. It works.

      3. Thank you! This worked for me!

      4. yes its working thanks alot

      5. WHY WE WAS USE CHAR(160),AND LIKE THIS WE HAVE SOME MANY OPTIONS LIKE CHAR(1),CHAR(30),CHAR(144)

      6. What a fantastic Solution given..... Thanks a lot

      7. This works!
        Used it to remove a single trailing space in a number column

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