# Excel: How to convert “string” to a “number”?

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?

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

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

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

• 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

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.)

• Karl, this answer has me stumped. The only way I could see it working is if there is a locale variation where comma is used as the decimal delimiter. – fixer1234 May 11 '17 at 16:05

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

``````=VALUE("5,00") // Returns integer 5