Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a user with a query from an SQL Database that returns a series of string in this form:


These are really numbers, however they cannot be summed. The correct way to fix this might be to cast the data in the SQL query, but then the Excel query viewer can't display it. As a workaround I do

 = <CELL> +1 -1.

Is there a more elegant solution?

share|improve this question
up vote 24 down vote accepted

I think the value function can be useful for you

 = VALUE(< CELL >)


In spanish we use the function VALOR:

 = VALOR(< CELL >)

i think it should be the same on english

share|improve this answer
Exactly what I was looking for! I thought there might be an eval() or such function -- but this is it! – SirStan Aug 20 '09 at 13:54
glad to be helpful !!! – Jhonny D. Cano -Leftware- Aug 20 '09 at 14:00

I use this little macro

Sub txttonum()
    For Each tcell In Selection.Cells
        If TypeName(tcell.Value) = "String" Then
            tcell.Value = Val(tcell.Value)
        End If
End Sub

Which converts all the highlighted cells to numbers if they're strings

share|improve this answer
This won't work on a database query area unfortunately. – SirStan Aug 20 '09 at 13:54
Good point, I don't think there's any way to fix the data in situ if it's coming from a query. I guess the value function is the best one for you. – Col Aug 20 '09 at 15:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .