+ Reply to Thread
Results 1 to 8 of 8

Removing Extra Whitespace

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Removing Extra Whitespace

    Maybe I haven't been specific enough in my searches on this website and throughout the Internet world...

    I have written a VBA in Excel 2007 to open a text file (not necessarily a .txt, but you can view it using word/notepad) and put it into another sheet in the workbook. Unforunately I cannot properly sort this data into columns because there aren't consistent delimiters and I have multiple spaces in different places. I understand Trim only works for removing from the beginning and end of the string. I've come across many codes and functions but nothing has really worked the way I need them to work. I still need to keep one space in between words/numbers so I can sort them into columns for later analysis. This is the part of my code that I can't get to work properly....

    Please Login or Register  to view this content.
    Above this I have ActiveSheet.Range("A:A").Select so it selects the column that I need to apply this to.

    Am I missing something? Is this too complicated?

    Thank you!
    Last edited by sperry2565; 07-10-2013 at 04:12 PM.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Removing Extra Whitespace

    Probably not the best but one example:
    Please Login or Register  to view this content.
    this would return "a d c d a d w d d d d" which I think is what you want?

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Removing Extra Whitespace

    Another way:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-02-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Removing Extra Whitespace

    Thanks Yudlugar. But I'm prompting the user to load the file so I have no idea what the file or the spacing looks like. Is there a way to specify the newly created worksheet could be the string? Or maybe that it's the range...I'm going to try a few things and if it works I'll post it and mark this as solved so if anyone else needs this they can find it!

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Removing Extra Whitespace

    Of course, they were just examples. If you wanted them as a function:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-02-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Removing Extra Whitespace

    Okay...I put something together that wasn't a function. I'm relatively new at this, would the function be faster? Either way this is what I've smashed together to get it to work:

    Please Login or Register  to view this content.
    Granted that is within my code....
    Last edited by sperry2565; 07-10-2013 at 04:13 PM.

  7. #7
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Removing Extra Whitespace

    No that is fine.

  8. #8
    Registered User
    Join Date
    07-02-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Removing Extra Whitespace

    Thank you! Just from looking at your code I have a lot to learn about visual basic still...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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