Results 1 to 5 of 5

Thread: VBA trim data with space not from spacebar
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA trim data with space not from spacebar

    I have a two full columns of data that I need to trim. I can see the extra space at the end, but it's not a simple space from the spacebar. This data is downloaded from an Oracle database which as the source, I have no control over.

    I used =CODE(RIGHT(V2)) to see what the space is and I get the value of 9. Is this the right way to determine what the character and the end is?

    Here is what the data looks like.

    BMOEAE
    BMOEA0
    BMOEAC

    How can I use VBA to trim column V and column Z in one shot?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,253
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA trim data with space not from spacebar

    Try
    Code:
    Sub FryGirl()
    Range("V:V,Z:Z").Replace Chr(9), "", xlPart, , , , False, False
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA trim data with space not from spacebar

    Are there always 6 characters then the Oracle character, whatever it is?

    Why not just use LEFT to take the left-most 6 or use SUBSTITUTE to change that code to whatever else you wan it to be?

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,016
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA trim data with space not from spacebar

    @Fluff, Thank you that works great.

    @ Kweaver, column z is 8 characters, but Fluff's code did it. Thanks.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,253
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA trim data with space not from spacebar

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •