Text cell and number cell

andonny

Board Regular
Joined
Mar 11, 2002
Messages
220
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi,

Try,

Code:
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
 
Upvote 0
Select your values, choose the Data | Text to Columns... menu command and press [ Finish ].
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top