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

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

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

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

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

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

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

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

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

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

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

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