+ Reply to Thread
Results 1 to 7 of 7

remove whitespaces

  1. #1
    Forum Contributor
    Join Date
    06-14-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    186

    remove whitespaces

    I am trying to remove leading spaces in my text using VBA as I am trying to write the contents to a text file.
    If the sentence has leading whitespaces then this has an affect on where this sentence starts in my text file - it will be indented from the left and I don't want that as I would like it to start from the beginning.

    Capture.PNG

    From the above image, you can see that my temp3 variable has leading whitespaces.
    I want to remove it and so I used this code below:
    Please Login or Register  to view this content.
    but that is not doing it.

    Then I tried using the trim function ie.
    Please Login or Register  to view this content.
    ) and that doesn't do either.

    Does anyone know how to do it?

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: remove whitespaces

    Hi,

    Try
    Please Login or Register  to view this content.
    in case the spaces aren't spaces, but are other non-printables.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Contributor
    Join Date
    06-14-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    186

    Re: remove whitespaces

    i tried that but no luck

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb

    Hi,

    so that's very not space, see what returns the VBA function AscW then you can replace this char code using the vba function ChrW …

  5. #5
    Forum Contributor
    Join Date
    06-14-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    186

    Re: remove whitespaces

    Hi Marc,

    How do I use the VBA function AscW?
    What parameter do I need to pass to it?

    Sorry, I am not familiar with this.

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: remove whitespaces

    Please Login or Register  to view this content.
    The above code should remove Leading and Trailing Spaces and Tabs.

    The following sample code uses VBA functions Trim(), LTrim(), RTrim(), and the Worksheet Function Trim() which behaves differently than the VBA Trim() function.
    Please Login or Register  to view this content.
    Lewis
    Last edited by LJMetzger; 02-22-2020 at 03:09 PM.

  7. #7
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: remove whitespaces

    Quote Originally Posted by mp3909 View Post
    ... leading whitespaces ...
    What is the result of the following code for you ?
    Please Login or Register  to view this content.

+ 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. Replies: 6
    Last Post: 10-19-2018, 02:44 PM
  2. Replies: 9
    Last Post: 11-23-2016, 12:22 PM
  3. [SOLVED] Escape whitespaces in formula strings
    By Carsti in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-13-2016, 11:34 AM
  4. excel macro to remove specific columns and rows + remove duplicate
    By garrywelson in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-17-2013, 12:03 PM
  5. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  6. Macro, to remove commas and remove speach marks
    By 5h1l in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-01-2012, 12:06 PM
  7. Replies: 0
    Last Post: 03-11-2010, 10:34 AM

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