Text cell and number cell

[andonny]

Hi,
I have just come across a problem. I extracted from a database fields which have numbers in it but when they are received in Excel they are actually text. Now I am faced with the problem of converting them to a number field. I wanted to use the vlookup function and wanted to compare two numbers but because one is a text field and the other a number it returns #N/A.

Thanks for your help
Andonny

[Jay Petrulis]

Hi,

Try,

Sub tester()
Dim Cell As Range, Rng1 As Range

Set Rng1 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:A"))

For Each Cell In Rng1
If Not IsEmpty(Cell) And Not Cell.HasFormula And IsNumeric(Cell) Then
    Cell = CDbl(Cell)
End If
Next Cell
End Sub

Note that this code also converts mainframe downloads thathave trailing negative signs (4- for example).

Adjust your range to suit.

Bye,
Jay

[Mark W.]

Select your values, choose the Data | Text to Columns... menu command and press [ Finish ].