+ 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
    641

    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 - For the whole of 2024

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

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

    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
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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
    641

    Re: Removing leading spaces

    Ok Thanks. That's working perfectly.

    Problem solved!


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

    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
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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 Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    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
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    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 Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    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)

Similar Threads

  1. [SOLVED] VBA Trim Leading Spaces
    By warp765 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-16-2020, 04:17 PM
  2. Removing Leading Spaces in a column of data with VBA Code
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-06-2018, 11:15 AM
  3. [SOLVED] Trouble removing leading spaces
    By jp001 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2017, 05:06 AM
  4. How to remove leading spaces
    By Terressa in forum Excel General
    Replies: 4
    Last Post: 06-27-2015, 07:45 PM
  5. Delete Trailing Spaces without Removing leading zeros WITHOUT TRIM
    By Zach51215 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-20-2013, 04:24 PM
  6. [SOLVED] Removing leading and trailing spaces
    By mangesh in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-18-2013, 05:25 AM
  7. [SOLVED] How do I add leading spaces to a value?
    By Chris Brown in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2006, 01:35 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