I am having issues converting numbers stored as text back to numbers.



Normally, the VALUE function should solve this, right?

Somehow it does not. I get an error when trying to convert "10.00" to a number using the VALUE function.

I attach a sheet to demo.

Just to pre-empt three things that won't work for me:

The scope of this file is such that it's not an option to manually convert the text to numbers using that error pop-up.

Unfortunately, I cannot fix the problem "upstream". My sheet will be receiving numbers stored as text.

Macros are also not an option. It must be a formula.

Does anyone know a solution.

Best regards
Pieka

Attached Files

example of uncovertable numbers.xlsx (9.9 KB, 11 views)

Did adding zero to the value not work?
=VALUE(B2)+0

This might be of help.

Attached Files

example of uncovertable numbers_2.xlsx (10.5 KB, 11 views)

Why do you need a formula? Simply type a 0 anywhere on any worksheet, then copy it to the clipboard, then select the range to convert and use Paste Special>Values>Add. The range to processs can even span cells that contain text (but not formulae). Empty cells will end up containing 0s, regardless of whether you check the 'skip blanks' option.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I noted that the sheet in the uploaded file is named Feuil1 so I guessed that OP is French and therefore uses comma as decimal separator instead of dot. If I guessed correct I think my proposal will work.

Hi xor

Yes, I think your solution does address this problem well.

And well spotted

The trouble is that this sheet might be used either in France or outside of it, and I don't want it to break when opened on a non-French PC. Any ideas on how to make the solution robust? Can I set the "Region settings" on a sheet to not change when opened on another PC? (I will post another question about this...)

Best regards
P

The solution I suggested is nothing if not robust.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

What is decimal separator for you?


For me, decimal separator is comma and list separator is semicolon - so for me works the formula like
Or you simply select whole datarange, and replace all "." with ",".


About using workbook when having different local settings - so long you have fields with numbers in it formatted as General or Number, decimal separator is adjusted automatically. I.e. when I have a value 1,2 in some cell formatted as General or Number, and someone in US opens the same file, he/she sees 1.2 in same cell. Problems starts with numbers formatted as texts. And in Excel, when you e.g. format the range in text format to General or Number format, all numeric values there remain texts until you don't edit all fields.


In your case, the real solution is not to find some formula to convert values - then you will have problems for users with different local settings. You have to start with reason, why you got numbers which Excel interprets as text. I.e. when you get in range B2 : D2 real numbers instead numeric strings, you don't have to convert anything anymore.

In your french workbook perhaps:
I think the formula semicolons automatically get changed to commas if opening it in an English version of Excel.
edit:
Or perhaps: