+ Reply to Thread
Results 1 to 10 of 10

Removing leading spaces

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    152

    Removing leading spaces

    I'm using this code in an attempt to remove leading spaces in my text :

    Please Login or Register  to view this content.

    Unfortunately it's not working. The leading spaces are still there.

    If I take these actions manually (select whole text - centre align - left align - go to top of document) then it works fine and removes the leading spaces , but no joy when run in my macro.

    I'm puzzled. If anyone can help out , I'd be grateful.
    Last edited by CDandVinyl; 05-22-2020 at 11:28 AM.
    Using Excel , Word and Access 2003 - May 1st 2020

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,075

    Re: Removing leading spaces

    Based on your description, I am not clear on what you mean by "leading spaces." This code does not remove any spaces, nor do the manual steps you described. Also, you should not have to center first before you left-align. "Leading spaces" usually means spaces inserted at the beginning of a paragraph.

    Could you attach a Word file with some text that shows the "before" situation, and describe what you want the "after" situation to look like?
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    152

    Smile Re: Removing leading spaces

    Thanks for getting back. I found this method here :

    https://techwelkin.com/how-to-remove...ces-in-ms-word

    Essentially , where certain lines of text start with a space (perhaps left after a search and replace operation , for example) , the act of highlighting the text and then formatting to centre and then left removes these spaces.

    I tried it manually and do find it works. I tried to make a VBA routine and find it didn't have the same effect.

    I'll attach a file with a before and after shown.
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,075

    Re: Removing leading spaces

    Try this instead. It explicitly removes leading spaces instead of relying on Word magical undocumented processing to do it. I have no idea why your manual methods works.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    152

    Re: Removing leading spaces

    Ok Thanks. That's working perfectly.

    Problem solved!


  6. #6
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,390

    Re: Removing leading spaces

    Quote Originally Posted by 6StringJazzer View Post
    This code does not remove any spaces, nor do the manual steps you described.
    On the contrary, if you centre a selected range using the GUI, any leading and trailing spaces will be removed.
    The VBA equivalent is:
    Please Login or Register  to view this content.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,075

    Re: Removing leading spaces

    Quote Originally Posted by macropod View Post
    On the contrary, if you centre a selected range using the GUI, any leading and trailing spaces will be removed.
    The VBA equivalent is:
    Please Login or Register  to view this content.
    To make my statement more precise, the removal of spaces is a side effect of centering then left-justifying text. It in fact does remove them, but there is no logic that says shifting text back and forth should remove spaces. The send keys replicates that manual action.

    It is a matter of opinion, but my opinion is that code that explicitly removes spaces is much clearer than code that invokes keys to do something that makes marginal sense to start with.

  8. #8
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,390

    Re: Removing leading spaces

    Quote Originally Posted by 6StringJazzer View Post
    It is a matter of opinion, but my opinion is that code that explicitly removes spaces is much clearer than code that invokes keys to do something that makes marginal sense to start with.
    True, but yours only removes the spaces before the text. Wildcards are also not needed. Try:
    Please Login or Register  to view this content.

  9. #9
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,075

    Re: Removing leading spaces

    Quote Originally Posted by macropod View Post
    True, but yours only removes the spaces before the text.
    I thought that was the request...?
    Wildcards are also not needed. Try:
    Brilliant.

  10. #10
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,390

    Re: Removing leading spaces

    Quote Originally Posted by 6StringJazzer View Post
    I thought that was the request...?Brilliant.
    As expressed, yes but when centring a selected range using the GUI, all leading and trailing white space (e.g. spaces and tabs) will be removed. My code replicates that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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