Convert Function to Sub

  • Thread starter Thread starter Paige
  • Start date Start date
P

Paige

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
 
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
 
Works perfectly. You are SUPER!! Thanks so much!

B Lynn B said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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




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
Click to expand...
.
Click to expand...
 
Thanks everyone - very good ideas which I'm definitely using!

B Lynn B said:
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 said:
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




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
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top