Number Stored As Text

[Paul W Smith]

What property of a cell should I check to determine whether that cell
contains a number stored as text?

I have some cells which Excel 2007 tells me, via the office assistant, are
numbers stored as text. I want to loop through all the cells in the used
range testing for and correcting this.

Paul Smith

 

[jseven]

What property of a cell should I check to determine whether that cell
contains a number stored as text?

I have some cells which Excel 2007 tells me, via the office assistant, are
numbers stored as text.  I want to loop through all the cells in the used
range testing for and correcting this.

Paul Smith

Wouldn't it be easier to multiply all of them by 1?

place a 1 in an empty cell, copy it, highlight all the cells you thin
might contain text, past special selecting multiply.

Done.

 

[J_Knowles]

Here are two macros:
A) convert text numbers to numbers
B) numbers to text numbers

You were interested in the TextToNumbers macro.

Sub TextToNumbers()
' Converting text numbers to real numbers
' Using the the used range for selection of cells
'
Dim cellval As Range
Dim myRng As Range
Set myRng = ActiveSheet.UsedRange
For Each cellval In myRng
cellval = cellval.Value
Next
End Sub

Sub NumbersToText()
' Converting numbers to text numbers
' Using the the used range for selection of cells
'
Dim cellval As Range
Dim myRng As Range
Set myRng = ActiveSheet.UsedRange
For Each cellval In myRng
If WorksheetFunction.IsNumber(cellval.Value) Then
cellval = "'" & cellval.Value
End If
Next
End Sub

HTH