convert a range of text into numbers

[Guest]

Hi

I have a variably sized range with numbers in text format.
I want to select them, and convert to numbers.

Thanks

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

Hi Rick,

I need in macro code

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

 

[Guest]

Gary,

it is working for 1 cell, but how do I select the whole range of
text-numbers to convert in numbers

 

[Guest]

Just select them in the worksheet before running the macro. You can select a
row or a column or a pile of cells.

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

Try this code where you specify your range in place of the range I show...

Dim MyRange As String
Dim R As Range
MyRange = "A11,A2:B2,A3:C3"
For Each R In Range(MyRange)
R.NumberFormat = "General"
R.Value = CDbl(R.Value)
Next

Rick

 

[Guest]

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")

 

[Guest]

It works
But how can I specify the range when is always different
Thanks

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Bill Renaud]

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")