Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-20-2019, 12:04 AM
levraininjaneer levraininjaneer is offline How to make text to number not region-sensitive Windows 7 64bit How to make text to number not region-sensitive Office 2013
Novice
How to make text to number not region-sensitive
 
Join Date: Dec 2019
Posts: 3
levraininjaneer is on a distinguished road
Default How to make text to number not region-sensitive

In English countries, the decimal separator in Excel is a period. In others, like France, a comma is used instead.



This is not normally an issue, because Excel does the thinking in the background and if you open a South African document with 97.4 in cell A1, lo and behold it shall be displayed automatically as 97,4

However, difficulty arises when we convert text to numbers.

See attached example.

In English Excel, you can do VALUE("97.4") = 97.4
In French Excel, you can do CNUM("97,4") = 97,4

But in France, CNUM("97.4") = #VALUE
One way to solve this is to do CNUM(SUBSTITUE("97.4"; "."; ",")) = 97,4

But I fear this may now cause #VALUE in English countries.

Maybe a simple solution could be
IFERROR(VALUE("97.4"), VALUE(SUBSTITUTE("97.4",".",",")))

But that feels a little daring. Not sure why.

Can anyone come up with an alternative solution to make my number conversion robust for any region?

Thanks in advance!
P
Attached Files
File Type: xlsx Number conversion example.xlsx (10.6 KB, 3 views)
Reply With Quote
Reply

Tags
conversion, number, text

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to make text to number not region-sensitive Mailmerge, show region only the first time. takamaz Mail Merge 3 02-26-2018 05:13 PM
How to make text to number not region-sensitive formula isnt case sensitive please help wobblypolarbear Excel 4 02-25-2017 08:23 AM
How to make text to number not region-sensitive Need to extract all lines containing EA, EB, EC...EZ. (case sensitive) Maxwell314 Excel 11 11-06-2015 08:52 AM
Case Sensitive (contains) Selection apolloman Excel 2 07-12-2011 04:50 AM
Saving as PDF causes prompt for printable region out of range chrisxyz Word 0 02-22-2010 05:58 PM


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


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