VBA Syntax for Excel's CLEAN Function

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA Syntax for Excel's CLEAN Function

  1. #1
    Board Regular peterv6's Avatar
    Join Date
    May 2005
    Location
    Leominster, MA
    Posts
    112
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Syntax for Excel's CLEAN Function

     
    Does anyone know how to code Excel's CLEAN function within VBA? I've tried numerous different ways, and none have worked.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,955
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something like this.
    Code:
       Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))
    If posting code please use code tags.

  3. #3
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Code:
    CleanedVariable = Application.WorksheetFunction.Clean(YourVariableToBeCleaned)

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  4. #4
    Board Regular peterv6's Avatar
    Join Date
    May 2005
    Location
    Leominster, MA
    Posts
    112
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie & Norrie, thank you both very much!

  5. #5
    Board Regular
    Join Date
    Aug 2009
    Location
    California
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Re: VBA Syntax for Excel's CLEAN Function

    Quote Originally Posted by Norie View Post
    Try something like this.
    Code:
       Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))
    Hi everyone - I have 'USB in range("A1"), that is atrophy(')&USB and I run the code above it doesn't work. However, if I put '7 in range("A1"), it works. I have been searching overall the web including Searching for Leading Apostrophes (Microsoft Excel) with this code:


    For Each c In Selection If c.PrefixCharacter = "'" Then c.Value = c.Value End If Next c

    Nothing seems to work for me

  6. #6
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,845
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Syntax for Excel's CLEAN Function

    The apostrophe just marks the cell as text. There's no need to remove it, but you can do

    Code:
    With Range("A1")
            .Value = .Formula
    End with

  7. #7
    Board Regular
    Join Date
    Aug 2009
    Location
    California
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Syntax for Excel's CLEAN Function

    Thanks shg. I counted len('USB), it returns 3. I think I will just leave it alone, but it just bugs me visually when I see the (') after I cleaned it.

  8. #8
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,845
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Syntax for Excel's CLEAN Function

    You're welcome.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    30,819
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Syntax for Excel's CLEAN Function

    Quote Originally Posted by peterv6 View Post
    Barrie & Norrie, thank you both very much!
    You might also be interested in seeing the "extended" CleanTrim UDF (user defined function) that I posted here...

    Trim all Cells in a Worksheet - VBA
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular
    Join Date
    Aug 2009
    Location
    California
    Posts
    264
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Syntax for Excel's CLEAN Function

      
    Rick - Tried your UDF. It's awesome! Thank you!

User Tag List

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
  •  

 

 
DMCA.com