remove non-numeric characters from a cell

[SWBodager]

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 ([email protected])

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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 ([email protected])

[SWBodager]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 ([email protected])
>
>
>

[Gord Dibben]

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 Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

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
<[email protected]> 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 ([email protected])

[Biff]

Glad to help! Thanks for the feedback.

Biff

"SWBodager" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 ([email protected])
>>
>>
>>

[Biff]

Glad to help! Thanks for the feedback.

Biff

"SWBodager" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 ([email protected])
>>
>>
>>

[Biff]

Glad to help! Thanks for the feedback.

Biff

"SWBodager" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 ([email protected])
>>
>>
>>

[REcord deleted error using parameter que]

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 Insert>Module. Paste the code in there. Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Run the macro by going to Tool>Macro>Macros.
>
> 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
> <[email protected]> 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 ([email protected])
>
>

[Gord Dibben]

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"
<[email protected]> 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 Insert>Module. Paste the code in there. Save the
>> workbook and hit ALT + Q to return to your workbook.
>>
>> Run the macro by going to Tool>Macro>Macros.
>>
>> 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
>> <[email protected]> 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 ([email protected])
>>
>>

[REcord deleted error using parameter que]

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"
> <[email protected]> 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 Insert>Module. Paste the code in there. Save the
> >> workbook and hit ALT + Q to return to your workbook.
> >>
> >> Run the macro by going to Tool>Macro>Macros.
> >>
> >> 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
> >> <[email protected]> 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 ([email protected])
> >>
> >>
>

[Gord Dibben]

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"
<[email protected]> 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"
>> <[email protected]> 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 Insert>Module. Paste the code in there. Save the
>> >> workbook and hit ALT + Q to return to your workbook.
>> >>
>> >> Run the macro by going to Tool>Macro>Macros.
>> >>
>> >> 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
>> >> <[email protected]> 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 ([email protected])
>> >>
>> >>
>>

[REcord deleted error using parameter que]

Thanks Gord, you are a legend.

After reading your reply it became clear that it failed becasue I had
already run the code and removed the text. Of course it was my feble attempt
at writing my orignal code, that kept removing characters from the cell, that
led me here in the first place. Your code is the perfect solution. This is
an aamazing resource. I hope you are richly rewarded.

Cheers and Merry Christmas
Jim

"Gord Dibben" wrote:

> 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"
> <[email protected]> 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"
> >> <[email protected]> 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 Insert>Module. Paste the code in there. Save the
> >> >> workbook and hit ALT + Q to return to your workbook.
> >> >>
> >> >> Run the macro by going to Tool>Macro>Macros.
> >> >>
> >> >> 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
> >> >> <[email protected]> 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 ([email protected])
> >> >>
> >> >>
> >>
>

[Gord Dibben]

Thanks for the feedback Jim.

Gord

On Tue, 13 Dec 2005 17:01:02 -0800, "REcord deleted error using parameter que"
<[email protected]> wrote:

>Thanks Gord, you are a legend.
>
>After reading your reply it became clear that it failed becasue I had
>already run the code and removed the text. Of course it was my feble attempt
>at writing my orignal code, that kept removing characters from the cell, that
>led me here in the first place. Your code is the perfect solution. This is
>an aamazing resource. I hope you are richly rewarded.
>
>Cheers and Merry Christmas
>Jim
>
>"Gord Dibben" wrote:
>
>> 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"
>> <[email protected]> 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"
>> >> <[email protected]> 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 Insert>Module. Paste the code in there. Save the
>> >> >> workbook and hit ALT + Q to return to your workbook.
>> >> >>
>> >> >> Run the macro by going to Tool>Macro>Macros.
>> >> >>
>> >> >> 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
>> >> >> <[email protected]> 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 ([email protected])
>> >> >>
>> >> >>
>> >>
>>

[[email protected]]

Hi,
Good work on a highly missed function (should be included as default by
Microsoft, shouldn't it?)

When I go for the selected range function early in this post, this
works by design if I select a range. But if I select a single cell, the
function takes a go for the whole sheet.

Any clue?

Kind regards,
Bonavox

[Gord Dibben]

Ammended code.......

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 = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)

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 If
End Sub


Gord

On 15 Dec 2005 01:01:46 -0800, [email protected] wrote:

>Hi,
>Good work on a highly missed function (should be included as default by
>Microsoft, shouldn't it?)
>
>When I go for the selected range function early in this post, this
>works by design if I select a range. But if I select a single cell, the
>function takes a go for the whole sheet.
>
>Any clue?
>
>Kind regards,
>Bonavox

[Bob Cohen]

Gord:

Your Macros is the answer to a problem I have had for years. How do I make
this Macro available to all my existing and new spreadsheets created? I am
not very knowledgeable of Macro programming. I added it to a new one I just
created but I would probably have to repeat that process every time I do one.

Keep up the good work, we appreciate it.

RDC (Bob)



"Gord Dibben" wrote:

> Ammended code.......
>
> 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 = Range(ActiveCell.Address & "," & Selection.Address) _
> .SpecialCells(xlCellTypeConstants)
>
> 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 If
> End Sub
>
>
> Gord
>
> On 15 Dec 2005 01:01:46 -0800, [email protected] wrote:
>
> >Hi,
> >Good work on a highly missed function (should be included as default by
> >Microsoft, shouldn't it?)
> >
> >When I go for the selected range function early in this post, this
> >works by design if I select a range. But if I select a single cell, the
> >function takes a go for the whole sheet.
> >
> >Any clue?
> >
> >Kind regards,
> >Bonavox
>

[Gord Dibben]

Bob

That's what Personal Macro Workbook is all about.

Store your macros in Personal.xls, place it in your XLSTART folder and it will
open when Excel is started.

Personal.xls is created the first time you record a Macro using Macro
Recorder.

Tools>Macro>Record New Macro. A dialog box will come up asking you name the
macro and where to place it. Pick Personal Macro Workbook from the dropdown.
Copy and paste a couple of cells then Stop Recording.

You now have a Personal.xls in your Office\XLSTART folder. You can go to
Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module.

You can add more macros by recording, typing or copying into the Module.

You can do a File>Save from there or better yet hit ALT + Q to return to the
Excel window.

Then with Personal.xls active, hit Window>Hide.

When you close Excel you will be asked if you want to save Personal.xls. Yes!

It will open hidden next time you start Excel.

I prefer to place my global macros in an add-in so I don't have to preface
macros with the Personal.xls filename.

As an added note:

To remove numbers and leave text change the above two lines in the Alpha
macro.

If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)

and change "Text" to "Numeric" in the msgbox



Gord

On Fri, 16 Dec 2005 09:52:03 -0800, Bob Cohen <Bob
[email protected]> wrote:

>Gord:
>
>Your Macros is the answer to a problem I have had for years. How do I make
>this Macro available to all my existing and new spreadsheets created? I am
>not very knowledgeable of Macro programming. I added it to a new one I just
>created but I would probably have to repeat that process every time I do one.
>
>Keep up the good work, we appreciate it.
>
>RDC (Bob)
>
>
>
>"Gord Dibben" wrote:
>
>> Ammended code.......
>>
>> 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 = Range(ActiveCell.Address & "," & Selection.Address) _
>> .SpecialCells(xlCellTypeConstants)
>>
>> 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 If
>> End Sub
>>
>>
>> Gord
>>
>> On 15 Dec 2005 01:01:46 -0800, [email protected] wrote:
>>
>> >Hi,
>> >Good work on a highly missed function (should be included as default by
>> >Microsoft, shouldn't it?)
>> >
>> >When I go for the selected range function early in this post, this
>> >works by design if I select a range. But if I select a single cell, the
>> >function takes a go for the whole sheet.
>> >
>> >Any clue?
>> >
>> >Kind regards,
>> >Bonavox
>>

[Alienvisitor]

Biff, Your solution worked fine for em just now. I had previously tried several other supposed solutions and none would do it - so very many thanks, Alien.