Thanks everyone - very good ideas which I'm definitely using!

"B Lynn B" wrote:

Good idea to account for the possibility that there could be formula cells in
the selection that should not be overwritten. But probably should leave in
the bit that makes the result UCase.

"Chip Pearson" wrote:

Try the following code:

Sub AAA()
Dim R As Range
Dim N As Long
Dim S As String
For Each R In Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
If R.Text < vbNullString Then
S = vbNullString
For N = 1 To Len(R.Text)
Select Case LCase(Mid(R.Text, N, 1))
Case "a" To "z", "0" To "9" '<<<<<<
S = S & Mid(R.Text, N, 1)
Case Else
' do nothing
End Select
Next N
R.Value = S
End If
Next R
End Sub

Select the cells to process and the run the code. This allows only "A"
to "Z" (upper or lower case) and "0" to "9". Modify the line marked
iwth <<<< if you have other characters that you want to allow.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Tue, 20 Apr 2010 10:21:06 -0700, Paige
wrote:

I have the following function which is used to extract the numeric and alpha
characters from a cell. Can someone help me 'convert' this to a sub, so
that, for example, with whatever range the user selects, the code will look
at each cell and basically remove everything that is not either alpha or
numeric? Example:
45 BJ}!12T would be converted to 45BJ12T

Public Function ExtractNT(TextString As String) As String
Dim x As Long
Dim sChar As String

ExtractNT = vbNullString
For x = 1 To Len(TextString)
sChar = Mid(TextString, x, 1)
If sChar = "0" And sChar <= "9" Or sChar Like "[a-zA-Z]" Then
ExtractNT = ExtractNT & UCase(sChar)
End If
Next x

End Function

.