View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\) Rick Rothstein \(MVP - VB\) is offline
external usenet poster
 
Posts: 2,202
Default how do i clean out all non-letter characters from an excel sheet

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