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

Sub CleanChars()

Dim CL As Range
Dim x As Long
Dim sChar As String
Dim ExtractNT As String
Application.ScreenUpdating = False

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

End Sub

"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

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

Works perfectly. You are SUPER!! Thanks so much!

"B Lynn B" wrote:

Sub CleanChars()

Dim CL As Range
Dim x As Long
Dim sChar As String
Dim ExtractNT As String
Application.ScreenUpdating = False

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

End Sub

"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

If sChar = "0" And sChar <= "9" Or sChar Like "[a-zA-Z]" Then

You can simplify the above line of code to this...

If sChar Like "[0-9a-zA-Z]" Then

--
Rick (MVP - Excel)



"B Lynn B" wrote in message
...
Sub CleanChars()

Dim CL As Range
Dim x As Long
Dim sChar As String
Dim ExtractNT As String
Application.ScreenUpdating = False

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

End Sub

"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

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

.

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

.