+ Reply to Thread
Results 1 to 16 of 16

Remove from over 2 blank space to 1 space.

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    440

    Remove from over 2 blank space to 1 space.

    Hi,

    I have a data file that query data from SQL and the result came out with First Name and Last Name without trim.
    Example:
    Please Login or Register  to view this content.
    Now, I need to remove all over than 2 space to only 1 space. Any way to do it fast?

    Thanks

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Remove from over 2 blank space to 1 space.

    Try using TRIM
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Remove from over 2 blank space to 1 space.

    No, I suggest you use replace and the searchstring is two spaces and replace for one, this will take care of all spaces, you can include trim across the entire field
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Remove from over 2 blank space to 1 space.

    or you find and replace 2 spaces with 1 and choose replace all, keep repeating till nothing is found.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Remove from over 2 blank space to 1 space.

    Using Application.Trim is more powerful than Trim
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Remove from over 2 blank space to 1 space.

    I agree with @YasserKhalil, the replace works in another way and you have to loop

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    440

    Re: Remove from over 2 blank space to 1 space.

    I am using this code but it does not replace all:
    Please Login or Register  to view this content.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Remove from over 2 blank space to 1 space.

    Not sure what your problem is with TRIM

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down will remove excess spaces, and leading and trailing spaces

  9. #9
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    440

    Re: Remove from over 2 blank space to 1 space.

    Quote Originally Posted by TMS View Post
    Not sure what your problem is with TRIM

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down will remove excess spaces, and leading and trailing spaces
    I am looking for VBA as I am doing some automation on it. Can I use trim to the whole column? or trim cell by cell till last row?

    Thanks

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Remove from over 2 blank space to 1 space.

    if your case :

    [code]
    Sub rmvSpaces()
    Dim cell as range
    for each Cell in Range("CN1:CN" & range("CN" & Rows.Count).end(xlup).row))
    cell.value = application.Trim(cell.Value)
    Next cell
    End Sub

    I typed it without testing so check if the syntax is correct for the last row calculation.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Remove from over 2 blank space to 1 space.

    Personally, I'd put the TRIM formula in a helper column, convert the results to values and, if required, overwrite the original. Likely to be quicker than a loop. Or, read the whole lot into an array, loop through the array using Application.Trim and then drop the array onto the original.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove from over 2 blank space to 1 space.

    Why not use Trim in sql Select clause in the first place?

  13. #13
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Remove from over 2 blank space to 1 space.

    Hi all- You don't need to loop. Use Application.Trim on the whole range, like this:
    Please Login or Register  to view this content.
    Last edited by leelnich; 02-09-2018 at 11:26 PM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  14. #14
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    440

    Re: Remove from over 2 blank space to 1 space.

    Awesome!!! Thanks
    Last edited by AliGW; 02-12-2018 at 11:49 AM. Reason: Unnecessary quotation removed.

  15. #15
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Remove from over 2 blank space to 1 space.

    You're most welcome, thank you for the rep! Please don't forget to mark your thread as SOLVED (Thread Tools above post #1). Regards – Lee
    Last edited by leelnich; 02-12-2018 at 09:14 PM.

  16. #16
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    440
    Quote Originally Posted by leelnich View Post
    You're most welcome, thank you for the rep! Please don't forget to mark your thread as SOLVED ([B]Thread Tools[/B above post #1). Regards – Lee

    Absolutely!

    Thanks

+ 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. [SOLVED] Data cleansing to remove blank space and "|"
    By Karnik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-27-2015, 09:07 AM
  2. VBA to remove the blank space in the cell
    By saranyabi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-06-2013, 10:10 AM
  3. remove space and blank
    By lassaad.toukabri in forum Excel General
    Replies: 5
    Last Post: 11-22-2013, 12:27 PM
  4. [SOLVED] How to remove empty space, unable to make pvot, because pvot calculate empty space
    By vengatvj in forum Excel Charting & Pivots
    Replies: 20
    Last Post: 10-29-2013, 12:43 PM
  5. [SOLVED] If Cell Starts with a Space, remove that space
    By Ocean Zhang in forum Excel General
    Replies: 2
    Last Post: 09-29-2012, 01:52 PM
  6. Remove Blank Space at end of the txt
    By tek9step in forum Excel General
    Replies: 6
    Last Post: 02-26-2010, 05:42 AM
  7. remove blank space in cell?
    By gunsmokegirl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2008, 02:26 PM

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