Excel Convert text to Number
 

Hi,
I need help on how to automate the conversion of a range of Excel cells from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel 9.0
Object lib. I've read some rows of data into an array and I set my Excel
Range = to the array which copies everything in quickly which is what I want.
Cell by cell is very slow with a hi number of records. Everything is fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then outside
fixes the problem and you can tell cause the warning is gone and numeric
formatting is applied. How can I automate this ConvertToNumber in code. If
I create thousands of these cells, the user can't be expected to convert each
one manually and I need to automate it. All my searches have netted zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert the
value of each of these cells to Number instead of Text? Your help would be
appreciated.

Excel Convert text to Number
 

Record a macro to macro to give you the basic syntax in VBA. A different
way, put 1 in a cell, copy, select the range, pastespecial, multiply. Again
record a macro.

Regards,
Peter T

"JoeBoynton" wrote in message
...
Hi,
I need help on how to automate the conversion of a range of Excel cells
from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel 9.0
Object lib. I've read some rows of data into an array and I set my Excel
Range = to the array which copies everything in quickly which is what I
want.
Cell by cell is very slow with a hi number of records. Everything is
fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown
being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then
outside
fixes the problem and you can tell cause the warning is gone and numeric
formatting is applied. How can I automate this ConvertToNumber in code.
If
I create thousands of these cells, the user can't be expected to convert
each
one manually and I need to automate it. All my searches have netted zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert the
value of each of these cells to Number instead of Text? Your help would
be
appreciated.

Excel Convert text to Number
 

Hummm, I'm trying to figure out what you've got there. Try this code:

Sub Remove_CR_LF()

With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(44), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

End Sub

You just have to know the ASCII Character...I think....


"JoeBoynton" wrote:

Hi,
I need help on how to automate the conversion of a range of Excel cells from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel 9.0
Object lib. I've read some rows of data into an array and I set my Excel
Range = to the array which copies everything in quickly which is what I want.
Cell by cell is very slow with a hi number of records. Everything is fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then outside
fixes the problem and you can tell cause the warning is gone and numeric
formatting is applied. How can I automate this ConvertToNumber in code. If
I create thousands of these cells, the user can't be expected to convert each
one manually and I need to automate it. All my searches have netted zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert the
value of each of these cells to Number instead of Text? Your help would be
appreciated.

Excel Convert text to Number
 

These two small procs should do the trick.
You can:
1a. Use a macro of your own to Set ConvertRng to the Range of cells you
want.
b. Call TextToNum(ConvertRng)

or

2a. Manually select the range of cells you want to convert.
b. Get to macros, Run ConvertText


Sub ConvertText()
Dim ConvertRng As Range
'Put your code to Set ConvertRng here
'or, select cells in the sheet and run this
'macro
Call TextToNum(ConvertRng)
End Sub


Sub TextToNum(Optional ConvertRng As Range = Nothing)
Dim Value
Dim Area As Range, OneCell As Range

'If you don't supply a range, the cells to be converted
'will be the ones you select prior to running this macro.
If ConvertRng Is Nothing Then Set ConvertRng = Selection

For Each Area In ConvertRng
For Each OneCell In Area
With OneCell
If IsNumeric(.Value) Then
Value = .Value
.NumberFormat = "general" 'or whatever numeric format you want
.Value = Value
End If
End With
Next OneCell
Next Area
End Sub
--
Neal Z


"JoeBoynton" wrote:

Hi,
I need help on how to automate the conversion of a range of Excel cells from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel 9.0
Object lib. I've read some rows of data into an array and I set my Excel
Range = to the array which copies everything in quickly which is what I want.
Cell by cell is very slow with a hi number of records. Everything is fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then outside
fixes the problem and you can tell cause the warning is gone and numeric
formatting is applied. How can I automate this ConvertToNumber in code. If
I create thousands of these cells, the user can't be expected to convert each
one manually and I need to automate it. All my searches have netted zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert the
value of each of these cells to Number instead of Text? Your help would be
appreciated.

Excel Convert text to Number
 

Sorry, forgot a most important item.

In the TextToNum proc,

put Application.ScreenUpdating = False
before this line of code

For Each Area In ConvertRng


put Application.ScreenUpdating = True

before the End Sub statement.

For thousands of cells, that should speed it up considerably.
--
Neal Z


"JoeBoynton" wrote:

Hi,
I need help on how to automate the conversion of a range of Excel cells from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel 9.0
Object lib. I've read some rows of data into an array and I set my Excel
Range = to the array which copies everything in quickly which is what I want.
Cell by cell is very slow with a hi number of records. Everything is fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then outside
fixes the problem and you can tell cause the warning is gone and numeric
formatting is applied. How can I automate this ConvertToNumber in code. If
I create thousands of these cells, the user can't be expected to convert each
one manually and I need to automate it. All my searches have netted zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert the
value of each of these cells to Number instead of Text? Your help would be
appreciated.

Excel Convert text to Number
 

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel cells
from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel
9.0
Object lib. I've read some rows of data into an array and I set my
Excel
Range = to the array which copies everything in quickly which is what I
want.
Cell by cell is very slow with a hi number of records. Everything is
fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown
being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then
outside
fixes the problem and you can tell cause the warning is gone and
numeric
formatting is applied. How can I automate this ConvertToNumber in code.
If
I create thousands of these cells, the user can't be expected to convert
each
one manually and I need to automate it. All my searches have netted
zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert
the
value of each of these cells to Number instead of Text? Your help would
be
appreciated.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

Excel Convert text to Number
 

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel cells
from
text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel
9.0
Object lib. I've read some rows of data into an array and I set my
Excel
Range = to the array which copies everything in quickly which is what I
want.
Cell by cell is very slow with a hi number of records. Everything is
fine,
except the cells with numbers were copied as text and those cells have
warning flags in the corner with the first 2 options in the dropdown
being:
"Number Stored As Text" and Convert To Number". Clicking on Convert to
Number, or even clicking inside the cell a couple of times and then
outside
fixes the problem and you can tell cause the warning is gone and
numeric
formatting is applied. How can I automate this ConvertToNumber in code.
If
I create thousands of these cells, the user can't be expected to convert
each
one manually and I need to automate it. All my searches have netted
zero,
zilcho, nada. Is there some hidden method somewhere that allows me to
convert a range or even loop thru the range cell by cell and convert
the
value of each of these cells to Number instead of Text? Your help would
be
appreciated.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

Excel Convert text to Number
 

How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..



Peter T;705601 Wrote:

Not here it doesn't -:)[color=blue]

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

Excel Convert text to Number
 

Sub test2()
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.Value = .Value
End With

Debug.Print VarType(Range("A1").Value) ' 8 or vbString

With Range("B1")
.Value = 1
.Copy
End With

Range("A1:A10").PasteSpecial Operation:=xlMultiply

Debug.Print VarType(Range("A1").Value) ' 5 or vbDouble

End Sub

Regards,
Peter T

"p45cal" wrote in message
...[color=blue]

How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..



Peter T;705601 Wrote:

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

Excel Convert text to Number
 

I suspect that could be because you're changing the format of the cells
and not changing it back. Try this on a virgin sheet:


VBA Code:
--------------------


Sub test2()
Debug.Print Range("A1").NumberFormat 'to establish pre-existing format i General
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.NumberFormat = "General" 'reset to default format, now you have numbers stored as text.
Debug.Print VarType(Range("A1").Value) ' 8 or vbString
.Value = .Value
Debug.Print VarType(Range("A1").Value) ' 5
End With
End Sub

--------------------





Peter T;705675 Wrote:

Sub test2()[color=blue]
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.Value = .Value
End With

Debug.Print VarType(Range("A1").Value) ' 8 or vbString

With Range("B1")
.Value = 1
.Copy
End With

Range("A1:A10").PasteSpecial Operation:=xlMultiply

Debug.Print VarType(Range("A1").Value) ' 5 or vbDouble

End Sub

Regards,
Peter T

"p45cal" wrote in message
...

How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..



Peter T;705601 Wrote:

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
'Excel Convert text to Number - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=197375)

'Microsoft Office Help - Microsoft Office Discussion - Excel VBA
Programming - Access Programming' (http://www.thecodecage.com/forumz)



--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

Excel Convert text to Number
 

I'll try your suggestion later, couldnt just now
btw i converted the cells from text to number 1 by 1 as follows:

Dim oRange = oWs.Range("A1").Resize(reszRow, rs.Fields.Count)
oRange.Value = TheArray

' rs is from legacy Acucobol vision files read with odbc/dao off of
Linux server

For myOrRow = 1 To ArrayRowCount + 1

' Extra row count above because I loaded headers into row 1

For myOrColumn = 1 To rs.Fields.Count
If IsNumeric(oRange(myOrRow, myOrColumn).Value) Then
oRange(myOrRow, myOrColumn).Value = oRange(myOrRow,
myOrColumn).Value.ToString * 1
End If
Next
Next

This seems to fix the problem, but its a time killer cause it's cell by cell

Thx for suggestions

"p45cal" wrote:


How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..




Peter T;705601 Wrote: [color=blue]

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

.

Excel Convert text to Number
 

That's just about the slowest way you could contrive to do it. You could try
doing as p45cal suggests (depends, it might not always work)
with rng
..numberformat = "General"
..value = .value
arr = .value
end with

or pastevalues with multiply 1 as I suggested (I also posted code sample)

If you don't want to change cells at all, read the entire range to an array,
then loop the array to change items as required (that'd be very
significantly faster than looping cells though probably not as quick as the
pastespecial multiply approach).

Regards,
Peter T


"JoeBoynton" wrote in message
...[color=blue]
I'll try your suggestion later, couldnt just now
btw i converted the cells from text to number 1 by 1 as follows:

Dim oRange = oWs.Range("A1").Resize(reszRow, rs.Fields.Count)
oRange.Value = TheArray

' rs is from legacy Acucobol vision files read with odbc/dao off of
Linux server

For myOrRow = 1 To ArrayRowCount + 1

' Extra row count above because I loaded headers into row 1

For myOrColumn = 1 To rs.Fields.Count
If IsNumeric(oRange(myOrRow, myOrColumn).Value) Then
oRange(myOrRow, myOrColumn).Value = oRange(myOrRow,
myOrColumn).Value.ToString * 1
End If
Next
Next

This seems to fix the problem, but its a time killer cause it's cell by
cell

Thx for suggestions

"p45cal" wrote:


How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..




Peter T;705601 Wrote:

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

.

Excel Convert text to Number
 

Yeah I know, but you didn't mention that originally. Also I'm almost sure
from memory that approach doesn't work in some scenarios (I might be wrong)

Regards,
Peter T

"p45cal" wrote in message
...[color=blue]

I suspect that could be because you're changing the format of the cells
and not changing it back. Try this on a virgin sheet:


VBA Code:
--------------------


Sub test2()
Debug.Print Range("A1").NumberFormat 'to establish pre-existing format i
General
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.NumberFormat = "General" 'reset to default format, now you have numbers
stored as text.
Debug.Print VarType(Range("A1").Value) ' 8 or vbString
.Value = .Value
Debug.Print VarType(Range("A1").Value) ' 5
End With
End Sub

--------------------





Peter T;705675 Wrote:

Sub test2()
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.Value = .Value
End With

Debug.Print VarType(Range("A1").Value) ' 8 or vbString

With Range("B1")
.Value = 1
.Copy
End With

Range("A1:A10").PasteSpecial Operation:=xlMultiply

Debug.Print VarType(Range("A1").Value) ' 5 or vbDouble

End Sub

Regards,
Peter T

"p45cal" wrote in message
...

How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..



Peter T;705601 Wrote:

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
'Excel Convert text to Number - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=197375)

'Microsoft Office Help - Microsoft Office Discussion - Excel VBA
Programming - Access Programming' (http://www.thecodecage.com/forumz)



--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz

Excel Convert text to Number
 

so after:

VBA Code:
--------------------


oRange.Value = TheArray
--------------------



just have:

VBA Code:
--------------------


Orange.Value = Orange.Value
--------------------



you could also temporarily put the line:

VBA Code:
--------------------


Orange.numberformat = "General"
--------------------


before the -orange.value = orange.value- line but I'd expect you
wouldn't need that after the first run, especially if there are special
formats you want to keep in that range?

Anyway, try it and see, fingers crossed. At least it should be quick.

Afterthought: you don't have to do this for the whole range, you could
do it for just a few columns:

VBA Code:
--------------------


Orange.columns(2).value = Orange.columns(2).value
--------------------


or for mo

VBA Code:
--------------------


Set rngToMakeNos = Union(orange.Columns(3), orange.Columns(5), orange.Columns(7))
rngToMakeNos.Value = rngToMakeNos.Value
--------------------







JoeBoynton;705723 Wrote:

I'll try your suggestion later, couldnt just now
btw i converted the cells from text to number 1 by 1 as follows:

Dim oRange = oWs.Range("A1").Resize(reszRow, rs.Fields.Count)
oRange.Value = TheArray

' rs is from legacy Acucobol vision files read with odbc/dao off of
Linux server

For myOrRow = 1 To ArrayRowCount + 1

' Extra row count above because I loaded headers into row 1

For myOrColumn = 1 To rs.Fields.Count
If IsNumeric(oRange(myOrRow, myOrColumn).Value) Then
oRange(myOrRow, myOrColumn).Value = oRange(myOrRow,
myOrColumn).Value.ToString * 1
End If
Next
Next

This seems to fix the problem, but its a time killer cause it's cell by
cell[color=blue]

Thx for suggestions

"p45cal" wrote:


How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..




Peter T;705601 Wrote:

Not here it doesn't -:)

Regards,
Peter T


"p45cal" wrote in message
...

the likes of:


VBA Code:
--------------------


Range("A1:M200")=Range("A1:M200").value
--------------------



seems to work here.


JoeBoynton;705352 Wrote:

Hi,
I need help on how to automate the conversion of a range of
Excel


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: 'Excel Convert text to Number - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=197375)

'Microsoft Office Help - Microsoft Office Discussion - Excel VBA
Programming - Access Programming' (http://www.thecodecage.com/forumz)

.



--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=197375

http://www.thecodecage.com/forumz