#1




Reliable way to convert text to number with a formula
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 preempt 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 popup. 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 
#2




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




This might be of help.

#4




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] 
#5




Reliable way to convert text to number with a formula
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.

#6




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 nonFrench 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 
#7




The solution I suggested is nothing if not robust.
__________________
Cheers, Paul Edstein [Fmr MS MVP  Word] 
#8




What is decimal separator for you?
For me, decimal separator is comma and list separator is semicolon  so for me works the formula like Code:
=VALUE(SUBSTITUTE(B2;".";",")) 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. 
#9




In your french workbook perhaps:
Code:
=IFERROR(VALUE(B3);VALUE(SUBSTITUTE(B3;".";","))) edit: Or perhaps: Code:
=SIERREUR(CNUM(B3);CNUM(SUBSTITUE(B3;".";","))) 
Tags 
conversion, text, value_function 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Formula to convert dates into text/values  Marcia  Excel  2  07012019 06:53 AM 
Attendance table Text Value Convert to Number  NickFazer  Excel  2  11082018 01:29 AM 
Cannot Convert Text Cell to number format to be able to sort the data  jyfuller  Excel  10  06192013 05:31 PM 
Convert Formula Result to Static Text  MYGS  Excel  16  01212013 08:18 AM 
Convert Number to Text  devcon  Word  0  07102010 01:16 AM 