Excel Convert text to Number

[JoeBoynton]

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.

 

[Peter T]

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

 

[RyGuy]

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....

 

[Neal Zimm]

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 Zimm]

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.

 

[p45cal]

the likes of:


VBA Code:

 

[Peter T]

Not here it doesn't -

Regards,
Peter T

the likes of:


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


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



seems to work here.





--
p45cal

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

http://www.thecodecage.com/forumz

 

[p45cal]

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

Not here it doesn't -

Regards,
Peter T

 

[Peter T]

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

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

 

[p45cal]

I suspect that could be because you're changing the format of the cell
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

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

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

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

 

[JoeBoynton]

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

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

 

[Peter T]

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

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

 

[Peter T]

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

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

 

[p45cal]

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 yo
wouldn't need that after the first run, especially if there are specia
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 coul
do it for just a few columns:

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


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


or for more:

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


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







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 b cell

Thx for suggestions