Scrubbing a worksheet

[cuetipper]

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.

 

[mole999]

Re: Scrubbing a wporksheet

try this

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 ~

 

[GR00007]

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.

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

 

[cuetipper]

Re: Scrubbing a wporksheet

mole999, thanks. But that deleted all my data.


Gr00007, how do I run the funtion?

 

[GR00007]

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.

 

[mole999]

Re: Scrubbing a wporksheet

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