Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-19-2019, 11:25 AM
levraininjaneer levraininjaneer is offline Reliable way to convert text to number with a formula Windows 7 64bit Reliable way to convert text to number with a formula Office 2013
Novice
Reliable way to convert text to number with a formula
 
Join Date: Dec 2019
Posts: 3
levraininjaneer is on a distinguished road
Default 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 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
File Type: xlsx example of uncovertable numbers.xlsx (9.9 KB, 9 views)
Reply With Quote
  #2  
Old 12-19-2019, 01:49 PM
NoSparks NoSparks is offline Reliable way to convert text to number with a formula Windows 7 64bit Reliable way to convert text to number with a formula Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Did adding zero to the value not work?
=VALUE(B2)+0
Reply With Quote
  #3  
Old 12-19-2019, 05:48 PM
xor xor is offline Reliable way to convert text to number with a formula Windows 10 Reliable way to convert text to number with a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

This might be of help.
Attached Files
File Type: xlsx example of uncovertable numbers_2.xlsx (10.5 KB, 9 views)
Reply With Quote
  #4  
Old 12-19-2019, 09:35 PM
macropod's Avatar
macropod macropod is offline Reliable way to convert text to number with a formula Windows 7 64bit Reliable way to convert text to number with a formula Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 12-19-2019, 09:55 PM
xor xor is offline Reliable way to convert text to number with a formula Windows 10 Reliable way to convert text to number with a formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default 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.
Reply With Quote
  #6  
Old 12-19-2019, 11:42 PM
levraininjaneer levraininjaneer is offline Reliable way to convert text to number with a formula Windows 7 64bit Reliable way to convert text to number with a formula Office 2013
Novice
Reliable way to convert text to number with a formula
 
Join Date: Dec 2019
Posts: 3
levraininjaneer is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 12-20-2019, 12:09 AM
macropod's Avatar
macropod macropod is offline Reliable way to convert text to number with a formula Windows 7 64bit Reliable way to convert text to number with a formula Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by levraininjaneer View Post
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?
The solution I suggested is nothing if not robust.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 12-20-2019, 12:12 AM
ArviLaanemets ArviLaanemets is offline Reliable way to convert text to number with a formula Windows 8 Reliable way to convert text to number with a formula Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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;".";","))
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.
Reply With Quote
  #9  
Old 12-22-2019, 04:47 PM
p45cal's Avatar
p45cal p45cal is offline Reliable way to convert text to number with a formula Windows 10 Reliable way to convert text to number with a formula Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In your french workbook perhaps:
Code:
=IFERROR(VALUE(B3);VALUE(SUBSTITUTE(B3;".";",")))
I think the formula semicolons automatically get changed to commas if opening it in an English version of Excel.
edit:
Or perhaps:
Code:
=SIERREUR(CNUM(B3);CNUM(SUBSTITUE(B3;".";",")))
Reply With Quote
Reply

Tags
conversion, text, value_function

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reliable way to convert text to number with a formula Formula to convert dates into text/values Marcia Excel 2 07-01-2019 06:53 AM
Attendance table Text Value Convert to Number NickFazer Excel 2 11-08-2018 01:29 AM
Reliable way to convert text to number with a formula Cannot Convert Text Cell to number format to be able to sort the data jyfuller Excel 10 06-19-2013 05:31 PM
Convert Formula Result to Static Text MYGS Excel 16 01-21-2013 08:18 AM
Convert Number to Text devcon Word 0 07-10-2010 01:16 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:29 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft