+ Reply to Thread
Results 1 to 6 of 6

TRIM does not remove all excess spaces

  1. #1
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    TRIM does not remove all excess spaces

    Hello Board,

    I have a cell values in many Cell where there are more than one extra (unwanted) spaces, and to explain my problem, am showing it (only for illustration purpose) such spaces with "_".

    So, if my cell value (A1) is John_,

    while using TRIM(A1), i get result as John

    above is perfectly alright,

    But, if i have cell value (A1) is John___ (3 blank spaces), only one space is removed, and using TRIM(A1), i get the result like John__ (only one space is removed).

    Is there any solution, any substitute to TRIM, which can remove all extra blank spaces

    Thanks
    Last edited by analystbank; 01-20-2017 at 01:08 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: TRIM does not remove all excess spaces

    If the spaces after John are REGULAR spaces, TRIM will remove all 3. However, if you are NOT removing them all, you probabaly have a non-breaking space - CHAR(10) and will need this to do the job:

    =TRIM(SUBSTITUTE(A1,CHAR(10),""))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: TRIM does not remove all excess spaces

    Thanks Glenn, what if there are irregular spaces, like sometime, 2 leading extra spaces, sometime 5, or 4 and so on. For my analysis i dump some raw data, make it suitable to customise those label, and carry out further anlysis using VBA, want to ensure, regardless of extra spaces, it should all be removed.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: TRIM does not remove all excess spaces

    Try the formula I suggested using a range of representative data. It should be fine. If not, post a small sample file showing the problem.

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: TRIM does not remove all excess spaces

    Trim function always remove all blank space. Plz share / attach your file.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  6. #6
    Forum Contributor
    Join Date
    08-17-2013
    Location
    Asia
    MS-Off Ver
    Excel 2010
    Posts
    271

    Re: TRIM does not remove all excess spaces

    Quote Originally Posted by Glenn Kennedy View Post
    If the spaces after John are REGULAR spaces, TRIM will remove all 3. However, if you are NOT removing them all, you probabaly have a non-breaking space - CHAR(10) and will need this to do the job:

    =TRIM(SUBSTITUTE(A1,CHAR(10),""))
    this worked in current situation, will explore. Thanks.

    @avk, yes, TRIM should remove all spaces, but I was surprised to see it is not happening after first extra spaces, i tried with CLEAR formula also, but that also did not work.

    Thanks, it is resolved, now with Glenn, suggestion.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Remove Spaces, Trim?
    By cybercab in forum Excel General
    Replies: 3
    Last Post: 02-11-2023, 02:39 PM
  2. [SOLVED] How to Remove spaces from a cell value? Trim function not working
    By terry3218 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2016, 11:54 AM
  3. [SOLVED] Trim function must remove all spaces between words.
    By HerryMarkowitz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-16-2016, 10:04 AM
  4. [SOLVED] trim does not remove leading spaces?
    By inventorgeorge in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-28-2015, 01:19 PM
  5. Replies: 4
    Last Post: 10-08-2013, 05:10 PM
  6. Excess Spaces
    By jlkirk in forum Excel General
    Replies: 2
    Last Post: 12-18-2012, 04:02 PM
  7. How do you remove excess spaces from an Excel field?
    By sarah_jane in forum Excel General
    Replies: 1
    Last Post: 06-01-2005, 04:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1