Scrubbing a worksheet

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
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 ~
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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
 

cuetipper

Board Regular
Joined
Nov 9, 2018
Messages
63
Re: Scrubbing a wporksheet

mole999, thanks. But that deleted all my data.


Gr00007, how do I run the funtion?
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
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

Moderator
Joined
Oct 23, 2004
Messages
10,338
Office Version
2019, 2016, 2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,261
Messages
5,467,633
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top