Importing from SQL as a Number instead of Text

J

Jez

I have code that imports a query from SQL Server and pastes the results into
certain cells. This works great, my problem is that when I come to find the
Max number from a range of cells, they all seem to be as Text...

How can when importing them change them from Text to Number?
 
J

Joel

I can't tell what the problem is without seeing the SQL statements. The data
may be stored in the database (or website) in text format, th eSQL statements
may be converting the numbers to text, or the spreadsheet may be formated as
text.

Here are some thngs to check

1) Click on a cell and see if there is a single quote infront of the number
or a space in front of the number.
2) click on the cell and then check the format by going to menu Format -
Cels - Number ans seeing which format is highlighted. If the format is TEXT
change format to number or general.
 
J

Jez

All the Cells are already set to General, The Field I take them from in SQL
Server is an INT.
Clicking on the cell shows no single quote or space in front of the number

I dont know where to go from here
 
J

Joel

A easy way to really determine if a cell is Text or a number is to use the
formula
=Sum(A1)

If the formula returns zero then the cell A1 is Text, otherwise it will
return the number in A1. You can change th eformula to any cell or range of
cells like
=Sum(A1:A5) or you can use =Max(A1:A5)

One possibility is there is a problem with only some of the cells and not
all the cells. Experiment a try to isolate the problem by changing the range
of the cells in the formula(s) until you find which ones are problems.
 
J

Joel

You can use this simple macro to find the cells that are text. Cahnge the
range A1:D10 as required.


Sub findtext()

Set SearchRange = Range("A1:D10")

For Each cell In SearchRange
If Not IsNumeric(cell) Then
MsgBox ("Cell : " & cell.Address & " is Text")
End If
Next cell
End Sub
 
J

Jez

Thanks I tried that code over the whole range of data and spent time looking
at each cell. All cells I was looking to be as a number were and all the text
fields were text fields, so I dont understand why this doesnt work...

=MAX(C12,F12,I12,C24,F24)
 
Top