How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

Hi!

Try this:

Assume your data is in the range A1:A100.

In B1 enter this formula and copy down as needed:

=LOOKUP(1000,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Biff

"SWBodager" wrote in message
...
How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I
am
working with contain the percentage and the appropriate letter grade (i.e.
80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and
one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

Thank you very much for your help. This worked perfectly.

"Biff" wrote:

Hi!

Try this:

Assume your data is in the range A1:A100.

In B1 enter this formula and copy down as needed:

=LOOKUP(1000,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Biff

"SWBodager" wrote in message
...
How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I
am
working with contain the percentage and the appropriate letter grade (i.e.
80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and
one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

Glad to help! Thanks for the feedback.

Biff

"SWBodager" wrote in message
...
Thank you very much for your help. This worked perfectly.

"Biff" wrote:

Hi!

Try this:

Assume your data is in the range A1:A100.

In B1 enter this formula and copy down as needed:

=LOOKUP(1000,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

Biff

"SWBodager" wrote in message
...
How can I remove all non-numeric characters from a cell? I am trying
to
figure final grades for all the students in our school. The cells that
I
am
working with contain the percentage and the appropriate letter grade
(i.e.
80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and
one
or two characters (may or may not have a space seperating the numbers
from
the leters). Thanks for any help on this,
Scott Bodager )

Scott

This macro will remove all but numbers and decimal point(if one present)

Also strips spaces. Select range or column first then run.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

On Mon, 23 May 2005 10:41:18 -0700, SWBodager
wrote:

How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

Hi Gord,
I don't mean to hijact this thread but your macro may be just what I'm
looking for. However, I am uncertian where to enter the details of the sheet
and range if the data to be modified is always in the same sheet and range
(other than the sumary sheet that summarises the data). In otehr words I
don't want to have to select the ranges to be changed each time.

I would appreciate any help you can offer. If necessary I will start a new
thread.

Cheers
Jim

"Gord Dibben" wrote:

Scott

This macro will remove all but numbers and decimal point(if one present)

Also strips spaces. Select range or column first then run.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

On Mon, 23 May 2005 10:41:18 -0700, SWBodager
wrote:

How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

Jim

Just hard-code the Sheet and Range.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Sheets("Sheet1").Range("E1:H20") _
..SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord
On Mon, 12 Dec 2005 00:37:05 -0800, "REcord deleted error using parameter que"
icrosoft.com wrote:

Hi Gord,
I don't mean to hijact this thread but your macro may be just what I'm
looking for. However, I am uncertian where to enter the details of the sheet
and range if the data to be modified is always in the same sheet and range
(other than the sumary sheet that summarises the data). In otehr words I
don't want to have to select the ranges to be changed each time.

I would appreciate any help you can offer. If necessary I will start a new
thread.

Cheers
Jim

"Gord Dibben" wrote:

Scott

This macro will remove all but numbers and decimal point(if one present)

Also strips spaces. Select range or column first then run.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

On Mon, 23 May 2005 10:41:18 -0700, SWBodager
wrote:

How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

Thanks Gord,
I had tried that and kept getting the reply "no cells were found". So I
added "ActiveWorkbook", hoping to fix it. Same result.

This is my code:
Set rngRR = ActiveWorkbook.Sheets("UC GST Lgr SUM").Range("I11:I12") _
.SpecialCells(xlCellTypeConstants, _
xlTextValues)

I hate to be a pest but would very much appreciate if you could identify my
error.

Cheers
Jim



"Gord Dibben" wrote:

Jim

Just hard-code the Sheet and Range.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Sheets("Sheet1").Range("E1:H20") _
..SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord
On Mon, 12 Dec 2005 00:37:05 -0800, "REcord deleted error using parameter que"
icrosoft.com wrote:

Hi Gord,
I don't mean to hijact this thread but your macro may be just what I'm
looking for. However, I am uncertian where to enter the details of the sheet
and range if the data to be modified is always in the same sheet and range
(other than the sumary sheet that summarises the data). In otehr words I
don't want to have to select the ranges to be changed each time.

I would appreciate any help you can offer. If necessary I will start a new
thread.

Cheers
Jim

"Gord Dibben" wrote:

Scott

This macro will remove all but numbers and decimal point(if one present)

Also strips spaces. Select range or column first then run.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

On Mon, 23 May 2005 10:41:18 -0700, SWBodager
wrote:

How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )

The Sub will error out if no text to be found in range.

Could be all numbers, blanks, formulas or a combination.

Try this error-trapped version.


Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
On Error Resume Next
Set rngRR = Sheets("Sheet1").Range("E1:H20") _
.SpecialCells(xlCellTypeConstants, _
xlTextValues)

On Error GoTo endit
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum

Next intI
rngR.Value = strTemp
Next rngR
Exit Sub
endit:
MsgBox "No text values in range"

End Sub


Gord

On Mon, 12 Dec 2005 17:52:02 -0800, "REcord deleted error using parameter que"
icrosoft.com wrote:

Thanks Gord,
I had tried that and kept getting the reply "no cells were found". So I
added "ActiveWorkbook", hoping to fix it. Same result.

This is my code:
Set rngRR = ActiveWorkbook.Sheets("UC GST Lgr SUM").Range("I11:I12") _
.SpecialCells(xlCellTypeConstants, _
xlTextValues)

I hate to be a pest but would very much appreciate if you could identify my
error.

Cheers
Jim



"Gord Dibben" wrote:

Jim

Just hard-code the Sheet and Range.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Sheets("Sheet1").Range("E1:H20") _
..SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord
On Mon, 12 Dec 2005 00:37:05 -0800, "REcord deleted error using parameter que"
icrosoft.com wrote:

Hi Gord,
I don't mean to hijact this thread but your macro may be just what I'm
looking for. However, I am uncertian where to enter the details of the sheet
and range if the data to be modified is always in the same sheet and range
(other than the sumary sheet that summarises the data). In otehr words I
don't want to have to select the ranges to be changed each time.

I would appreciate any help you can offer. If necessary I will start a new
thread.

Cheers
Jim

"Gord Dibben" wrote:

Scott

This macro will remove all but numbers and decimal point(if one present)

Also strips spaces. Select range or column first then run.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben Excel MVP

On Mon, 23 May 2005 10:41:18 -0700, SWBodager
wrote:

How can I remove all non-numeric characters from a cell? I am trying to
figure final grades for all the students in our school. The cells that I am
working with contain the percentage and the appropriate letter grade (i.e. 80
C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one
or two characters (may or may not have a space seperating the numbers from
the leters). Thanks for any help on this,
Scott Bodager )