how do i clean out all non-letter characters from an excel sheet

[jgarbis]

I have an excel sheet and I need to clean out all non-letter
characters and keep only a-z, how do I do that?

 

[Pete_UK]

You could do it with a user-defined function (UDF) which examines each
character in the cell and only allows A-Z and a-z. Unfortunately, I
don't have enough time to put one together now, but maybe someone else
can.

Pete

 

[Gord Dibben]

This macro will delete all but A-Z in cells with constants.

What do you want done with formulas? Convert to values and strip also?

If so, make revision as noted at code lines below.

Public Sub StripAllButAZs()
''strips out everything except letters
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)

' Set myRange = Range(ActiveCell.Address & "," & Selection.Address)
' to include formula cells

If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 90) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP

 

[Harlan Grove]

This macro will delete all but A-Z in cells with constants.

What do you want done with formulas? Convert to values and strip also?

If so, make revision as noted at code lines below.

Public Sub StripAllButAZs() ....
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants) ....
If myRange Is Nothing Then Exit Sub

Or you could make error trap branch to a label at the end of the procedure,
which is rather clearer in what's intended.

If Not myRange Is Nothing Then

This is pointless given the preceding If statement.

For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _
(Asc(UCase(Mid(myStr, i, 1))) > 90) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If

For Excel 2000 and later, it'd be more efficient to use

Dim c As String * 1
:
For i = 1 To Len(myStr)
c = Mid$(myStr, i, 1)
If c Like "[!A-Za-z]" Then myStr = Replace(myStr, c, "", i)
Next i

With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

But how do you know calculation had been set to automatic to begin with?
Better to store the calculation setting then restore it at the end. And this
is another good reason to use On Error to branch to a label near the end BUT
ABOVE this final with block. Either that or put your first With block
*AFTER* your .SpecialCells call. You code called with no constant cells in
the selected range could turn calculation from automatic to manual
mysteriously.

 

[Harlan Grove]

For Excel 2000 and later, it'd be more efficient to use

Dim c As String * 1
:
For i = 1 To Len(myStr)
c = Mid$(myStr, i, 1)
If c Like "[!A-Za-z]" Then myStr = Replace(myStr, c, "", i)
Next i

....

More efficient still,

If myStr Like "*[!A-Za-z]*" Then
For i = 1 To Len(myStr)
c = Mid$(myStr, i, 1)
If c Like "[!A-Za-z]" Then myStr = Replace(myStr, c, "", i)
Next i
End If

 

[Rick Rothstein \(MVP - VB\)]

More efficient still,

If myStr Like "*[!A-Za-z]*" Then
For i = 1 To Len(myStr)
c = Mid$(myStr, i, 1)
If c Like "[!A-Za-z]" Then myStr = Replace(myStr, c, "", i)
Next i
End If

I believe repeated executions of Replace might not be as efficient as
multiple "string stuffing" followed by a single execution of the Replace
function...

If myStr Like "*[!A-Za-z]*" Then
For i = 1 To Len(myStr)
If Mid$(myStr, i, 1) Like "[!A-Za-z]" Then Mid$(myStr, i, 1) = " "
Next
myStr = Replace(myStr, " ", "")
End If

where I chose to use a blank space as the string stuffing character (but any
non-alpha character would do).

Rick

 

[CLR]

ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will do that nicely......

Vaya ocn Dios,
Chuck, CABGx3

 

[Harlan Grove]

...
....

I believe repeated executions of Replace might not be as efficient
as multiple "string stuffing" followed by a single execution of
the Replace function...

If myStr Like "*[!A-Za-z]*" Then
For i = 1 To Len(myStr)
If Mid$(myStr, i, 1) Like "[!A-Za-z]" Then _
Mid$(myStr, i, 1) = " "
Next
myStr = Replace(myStr, " ", "")
End If

where I chose to use a blank space as the string stuffing
character (but any non-alpha character would do).

Depends on the frequency of particular nonalpha characters. If the
nonalphas were random with few if any multiple instances in any cell,
then your approach may be faster. But if there were multiple instances
of particular characters, getting rid of all of them (and shortening
the string in the process) could be faster.

Also, you don't need to insert " " for instances of " " in mystr, so
make the second Like pattern "[!A-Za-z ]".

 

[Rick Rothstein \(MVP - VB\)]

Depends on the frequency of particular nonalpha characters. If the

nonalphas were random with few if any multiple instances in any cell,
then your approach may be faster. But if there were multiple instances
of particular characters, getting rid of all of them (and shortening
the string in the process) could be faster.

There is nothing like knowing the data you are working with when designing
an algorithm.

Also, you don't need to insert " " for instances of " " in mystr, so
make the second Like pattern "[!A-Za-z ]".

LOL... Good catch... It always surprises me how easy it is to overlook the
obvious.

Rick