Convert Function to Sub

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
 
B

B Lynn B

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
 
C

Chip Pearson

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
 
P

Paige

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...
 
R

Rick Rothstein

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...
 
B

B Lynn B

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...
 
P

Paige

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transposing Data 3
VBA code to extract m-coefficient in linear trendlines from ALL charts 3
Using a sub within a function 1
Convert Macro to custom function 1
Convert Words in Strings to Caps 5
VBA Multiple Line handling with Split() 1
vba Function code 1
Run a sub every time a function is used in a sheet 7

Top