Using =VALUE() but getting #VALUE for text

[Iain Garey]

Hi

I have a SQL Report which exports data from our accounts system in to Excel and one of the columns shows both numbers and text, however the format of this column is such that it doesn't recognise the numbers as actual numbers.

I have therefore created a new column within excel where I'd like to change the format of these "numbers" to actually be numbers and keep the text fields as they are.

I've tried to use the formula =VALUE(cell) which works for those that are numbers but returns #VALUE for the text field.

Example below

SQL (text) New Column using =VALUE() What I'd like but with the numbers being recognised as numbers
1 1 1
2 2 2
3 3 3
Yes #VALUE Yes
No #VALUE No
4 4 4

Many thanks

[MrShorty]

As explained in the help file (https://support.office.com/en-us/art...c-bc2d3953d8c2 ), if the input text cannot be interpreted as a number, then VALUE() returns the #VALUE error. You will need to nest your VALUE() function inside of some kind of decision function that reflects the exact logic you want. For example, =IFERROR(VALUE(A1),A1) will convert the value of A1 to a number if A1 is a convertable number stored as text, but will simply display the text in A1 if it cannot be converted to a number. (Help file for the IFERROR() function, if needed: https://support.office.com/en-us/art...6-63f3e417f611 ). Or something like =IF(ISNUMBER(VALUE(A1)),VALUE(A1),A1) will test the result of VALUE() to see if it is a number. If so, then it will convert the number as text to number. If not, then it will return the text string.

[Iain Garey]

Thank you, this works a treat.