Excel: How to convert "string" to a "number"?

Question

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

000123123.23000
000123123.23000
000123123.23000
000123123.23000
000123123.23000

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?

Answer 1

I think the value function can be useful for you

 = VALUE(< CELL >)

PD:

In spanish we use the function VALOR:

 = VALOR(< CELL >)

i think it should be the same on english

Answer 2

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
    Next
End Sub

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

Answer 3

I had the same problem, but the VALUE-function did not work in my tsv-file. But a small trick did the job:

1. select the column with the numbers in text-format

2. Ctrl+F --> replace . with ,

(Normally I would hate such solutions, but as said, I just post it here because the 'normal' way of solving it didn't work out for me.)

Answer 4

Depending of your locale settings, VALUE() may return an error.

If that's the case, maybe NUMBERVALUE() can help you.

VALUE vs NUMBERVALUE comparison

With a locale where decimal separator is a comma :

=VALUE("5,00") // Returns integer 5
=VALUE("5.00") // Throw an error

=NUMBERVALUE("5,00";",") // Returns integer 5
=NUMBERVALUE("5.00";".") // Returns integer 5