Results 1 to 6 of 6

Thread: Scrubbing a worksheet
Thanks Thanks: 0 Likes Likes: 0

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

    Default Scrubbing a worksheet

    So this seems it should be simple but i cant get it.
    I want to remove all non alpha charectors from a spreadsheet but not the spaces, which is where i am getting issues.

    So if I had [ "bob's Number*1 ten." ] the results would be [ bobs number1 ten ].


    Any ideas?

    Thanks.

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Scrubbing a wporksheet

    try this

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Application.ScreenUpdating = False
    With ActiveSheet
        Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
            xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
            xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="~*", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            End With
    Application.ScreenUpdating = True
    
    End Sub
    the tricky one is * where you have to preface with ~
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Apr 2015
    Posts
    184
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Scrubbing a wporksheet

    Here's a function that will only return alpha characters and space. If you want to include numbers as well, that can be added to the criteria if you want.
    Code:
    Function AlphaOnly(vTx As Range) As String
    '
    ' Macro1 Macro
    '
    vIn = vTx.Value
    vOut = ""
    For i = 1 To Len(vIn)
        If (Mid(vIn, i, 1) >= "A" And Mid(vIn, i, 1) <= "Z") Or (Mid(vIn, i, 1) >= "a" And Mid(vIn, i, 1) <= "z") Or Mid(vIn, i, 1) = " " Then
            vOut = vOut & Mid(vIn, i, 1)
        End If
    Next
    AlphaOnly = vOut
    '
    End Function

  4. #4
    Board Regular
    Join Date
    Nov 2018
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Scrubbing a wporksheet

    mole999, thanks. But that deleted all my data.


    Gr00007, how do I run the funtion?

  5. #5
    Board Regular
    Join Date
    Apr 2015
    Posts
    184
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Scrubbing a wporksheet

    Use in an empty cell where X2 is the cell you want scrubbed:
    =AlphaOnly(X2)
    You can then copy it down.
    The function needs to be in the workbook you're using, OR if you have a repository for functions or whatever in another workbook you could use the Insert Function on the Formulas tab, select user functions, and you should find it.

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,850
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Scrubbing a wporksheet

    Quote Originally Posted by cuetipper View Post
    mole999, thanks. But that deleted all my data.


    Gr00007, how do I run the funtion?
    didn't on the line supplied, * on its own will
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

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
  •