Page 1 of 2 12 LastLast
Results 1 to 10 of 11

VBA Syntax for Excel's CLEAN Function

This is a discussion on VBA Syntax for Excel's CLEAN Function within the Excel Questions forums, part of the Question Forums category; Does anyone know how to code Excel's CLEAN function within VBA ? I've tried numerous different ways, and none have ...

  1. #1
    Board Regular peterv6's Avatar
    Join Date
    May 2005
    Location
    Leominster, MA
    Posts
    110

    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
    65,979

    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

    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
    110

    Default

    Barrie & Norrie, thank you both very much!

  5. #5
    Board Regular
    Join Date
    Aug 2009
    Location
    California
    Posts
    256

    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
    shg
    shg is online now
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,396

    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
    256

    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
    shg
    shg is online now
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,396

    Default Re: VBA Syntax for Excel's CLEAN Function

    You're welcome.
    Microsoft MVP - Excel

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    15,909

    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? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  10. #10
    Board Regular
    Join Date
    Aug 2009
    Location
    California
    Posts
    256

    Default Re: VBA Syntax for Excel's CLEAN Function

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

Page 1 of 2 12 LastLast

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