Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Hi
I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Sub numerify()
Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
I have a variably sized range with numbers in text format.
I want to select them, and convert to numbers. Are you needing this in (macro) code or do you just have a range of data that you need to convert one time? If the latter, just enter 1 into a cell (as a number), press Ctrl+C, select your range of cells that have the text-numbers in it, click Edit/PasteSpecial in Excel's menu, select Multiply from the Operation section of the dialog box that appears and then click OK. Rick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Hi Rick,
I need in macro code "Rick Rothstein (MVP - VB)" wrote: I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Are you needing this in (macro) code or do you just have a range of data that you need to convert one time? If the latter, just enter 1 into a cell (as a number), press Ctrl+C, select your range of cells that have the text-numbers in it, click Edit/PasteSpecial in Excel's menu, select Multiply from the Operation section of the dialog box that appears and then click OK. Rick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Gary,
it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Just select them in the worksheet before running the macro. You can select a
row or a column or a pile of cells. -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Gary,
But how can I include in this macro a selection for variably sized range Because this is part of a bigger macro, I do not want select the range manually Thanks "Gary''s Student" wrote: Just select them in the worksheet before running the macro. You can select a row or a column or a pile of cells. -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Try this code where you specify your range in place of the range I show...
Dim MyRange As String Dim R As Range MyRange = "A1:D1,A2:B2,A3:C3" For Each R In Range(MyRange) R.NumberFormat = "General" R.Value = CDbl(R.Value) Next Rick "Faboboren" wrote in message ... Gary, But how can I include in this macro a selection for variably sized range Because this is part of a bigger macro, I do not want select the range manually Thanks "Gary''s Student" wrote: Just select them in the worksheet before running the macro. You can select a row or a column or a pile of cells. -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Here is a typical approach:
Dim rrGlobal As Range Sub main() Set rrGlobal = Range("A:A") Call numerify End Sub Sub numerify() Dim r As Range Count = 0 For Each r In rrGlobal If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub 1. we DIM a static, global, variable as a range (before the subs) 2. the main sub set the global and calls numerify 3. numerify uses the supplied range Naturally you would set rrGlobal to your desired range not Range("A:A") -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, But how can I include in this macro a selection for variably sized range Because this is part of a bigger macro, I do not want select the range manually Thanks "Gary''s Student" wrote: Just select them in the worksheet before running the macro. You can select a row or a column or a pile of cells. -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
It works
But how can I specify the range when is always different Thanks "Rick Rothstein (MVP - VB)" wrote: Try this code where you specify your range in place of the range I show... Dim MyRange As String Dim R As Range MyRange = "A1:D1,A2:B2,A3:C3" For Each R In Range(MyRange) R.NumberFormat = "General" R.Value = CDbl(R.Value) Next Rick "Faboboren" wrote in message ... Gary, But how can I include in this macro a selection for variably sized range Because this is part of a bigger macro, I do not want select the range manually Thanks "Gary''s Student" wrote: Just select them in the worksheet before running the macro. You can select a row or a column or a pile of cells. -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
I'm not sure I understand your question. You told Gary's Student that you
don't want to select the range manually, so I assume you somehow know the range you need to handle... just assign it to the MyRange variable in my code and then run the For Each loop I provided. If you have the actual range in code somewhere, then use that in place of Range(MyRange) in the code. If this hasn't helped you any, then tell me how your code "knows" the range of cells containing the text-numbers that you want to change into real numbers. Rick "Faboboren" wrote in message ... It works But how can I specify the range when is always different Thanks "Rick Rothstein (MVP - VB)" wrote: Try this code where you specify your range in place of the range I show... Dim MyRange As String Dim R As Range MyRange = "A1:D1,A2:B2,A3:C3" For Each R In Range(MyRange) R.NumberFormat = "General" R.Value = CDbl(R.Value) Next Rick "Faboboren" wrote in message ... Gary, But how can I include in this macro a selection for variably sized range Because this is part of a bigger macro, I do not want select the range manually Thanks "Gary''s Student" wrote: Just select them in the worksheet before running the macro. You can select a row or a column or a pile of cells. -- Gary''s Student - gsnu200754 "Faboboren" wrote: Gary, it is working for 1 cell, but how do I select the whole range of text-numbers to convert in numbers "Gary''s Student" wrote: Sub numerify() Dim r As Range Count = 0 For Each r In Selection If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary''s Student - gsnu200754 "Faboboren" wrote: Hi I have a variably sized range with numbers in text format. I want to select them, and convert to numbers. Thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert a range of text into numbers
Sounds like you want a subroutine that you can pass in a range of cells.
The following routine ignores cells that are blank or contain formulas. It only works on non-empty cells that contain text values that are numeric. '---------------------------------------------------------------------- Public Sub ChangeTextValuesToNumbers(MyRange As Range) Dim rngCell As Range If MyRange Is Nothing Then Exit Sub For Each rngCell In MyRange With rngCell If Not .HasFormula And Not IsEmpty(rngCell) _ Then If IsNumeric(rngCell) _ Then 'Convert value to double and paste back in cell. .Value = CDbl(.Value) End If End If End With Next rngCell End Sub To use, call it and pass in a range of cells, like so: ChangeTextValuesToNumbers Range("A1:A5") -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers stored as text to numbers | Excel Discussion (Misc queries) | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |