Converting exponential numbers to real numbers

Scripting Forums AppleScript | Mac OS X
#1

I’m having problems getting numbers from an Excel spreadsheet and into Adobe Illustrator file. When the numbers are larger than 10,000, they come out as exponentials. Here’s what I have:

tell application “Microsoft Excel”
set the_figure to (Value of (Cell 2 of Column 5 of Sheet 1)) – the number in this cell is 12316.54
end tell
set the_phrase to "The number is " & the_figure & “.”
tell application “Adobe Illustrator”
set text_block to make new text frame at layer 1 of current document with properties {contents:the_phrase, position:{100, 100}}
end tell

What I’d like to get is a block that reads “The number is 12316.54.”, but what I get instead is “The number is 1.231654E+4.”

Is there a simple solution?

#2

Hi John,

This from AppleScriptLnaguageGuide.pdf:

Real numbers that are greater than or equal to 10,000.0 or less than or equal to
0.0001 are converted to exponential notation when scripts are compiled.

What you can do is get the whole number part and the decimal part and concatenate as text.

gl,

#3

Here is a thread that you may find useful as well:

http://bbs.applescript.net/viewtopic.php?id=15413

#4

Script Removed - offsets incorrect

#5

Hi Adam,

1.2331654E+4 is not eequal to 12331654, unless I’m missing something there.

gl,

#6

My script turned out to be kind of long and doesn’t account for negative exponentials.


set n to 1.236154E+4
set t to (n as string)
set user_tid to AppleScript's text item delimiters
set AppleScript's text item delimiters to {"E"}
set temp_l to text items of t
set AppleScript's text item delimiters to {"."}
tell temp_l
	set {n, e} to {text items of item 1, (item 2 of temp_l) as integer}
end tell
tell n
	set item 2 to text 1 thru (e + 2) of (item 2 & "000000000000000")
	set item 1 to item 1 & (text 1 thru e of item 2)
	set item 2 to text (e + 1) thru -1 of item 2
end tell
set n to n as string
set AppleScript's text item delimiters to user_tid
n

Didn’t Nigel do one of these real to text things? I couldn’t find it.

gl,

#7

He did, Kel, and I have the same problem. I can’t find it - perhaps he’ll weigh in tomorrow morning (It’s now 12:30 AM in GB). Neither your, nor my solution would be hard to make deal with negative numbers and numbers with negative exponants, but the OP seemed to be dealing with positives. Did Nigel’s somehow leverage Unicode text’s larger bit count?

#8

Fixed it above kel. Left a piece out of the original scratch version (full of logs)

#9

Apple has a few subroutines for this. http://www.apple.com/applescript/guidebook/sbrt/pgs/sbrt.08.htm

Any of these will do the job:
on round_truncate(this_number, decimal_places)
on number_to_text(this_number)
on comma_delimit(this_number)

#10

Hi, chaps.

Thanks for your interest. My numToStr handler’s here in ScriptBuilders. I haven’t looked at it or thought about the problem for a couple of years, so I can’t remember why it’s done the way it is. It’s a general purpose number-to-string converter. You don’t have to know in advance if the number’s exponential or otherwise. It works with reals, integers, positives, negatives, and numbers between -1 and 1. It also contains a few shakes to iron out the floating-point difficulties that come with certain numbers “ such as 8483.38 and its similar, near neighbours. (Try compiling a few and you’ll see what I mean.)

I think there’s some way of formatting numeric strings in Unix, but I haven’t looked into it. The Satimage OSAX has a format command that allows some reasonably short code:

on numToStr(n)
	set n to n as number
	set ns to (format n into "############.###########") -- Needs Satimage OSAX.
	if (n's class is integer) then set ns to text 1 thru -3 of ns

	return ns
end numToStr
#11

And I’ve finally fixed mine for positive exponents, positive numbers:


getTfromN(9.87654321E+5)

to getTfromN(X)
	set n to X as text
	if n contains "+" then
		set O to offset of "+" in n -- find the exponent
		set p to text (O + 1) thru -1 of n -- the exponent value
		tell n to set T to text item 1 & text items 3 thru (O - 2) -- get the numbers
		tell T to set nT to text 1 thru (p + 1) & "." & text (p + 2) thru -1 -- stick in "."
		return nT
	else
		return n
	end if
end getTfromN
#12

Hi Adam,

I dared to add the function to add zeros if the decimal places are less than the exponent :wink:

getTfromN(9.8765E+5)

to getTfromN(X)
	set n to X as text
	if n contains "+" then
		set O to offset of "+" in n -- find the exponent
		set p to text (O + 1) thru -1 of n -- the exponent value
		tell n to set T to text item 1 & text items 3 thru (O - 2) -- get the numbers
		if ((p as integer) + 1) ≥ (count T) then
			set d to ((p as integer) + 1) - (count T)
			set nT to T
			repeat d times
				set nT to nT & "0"
			end repeat
		else
			tell T to set nT to text 1 thru (p + 1) & "." & text (p + 2) thru -1 -- stick in "."
		end if
		return nT
	else
		return n
	end if
end getTfromN
#13

:slight_smile: Thanks. Didn’t think of it. Tunnel vision getting the decimal point in the right place. (for you a comma would be appropriate)

#14

I know this topic has been on the shelf a while but I was inspired to give it a go. This includes a super-simple shell script conversion of exponential numbers in line one, and then some if-thens for placing some commas when the numbers get big. The largest number it spit out during tests with the included random number generator was this…

“‘2,012,009,890,299,000,053,078,911,865,592,313,973,011,672,255,274,113,821,952,938,596,545,074,236,404,122,939,660,629,558,622,321,119,207,482,003,043,218,878,507,374,303,208,823,402,681,339,224,450,318,609,019,554,750,335,013,720,074,481,739,669,129,534,518,591,488.00’”

If there’s a good reason not to do it this way, let me know. (I should note that it currently rounds off to two spaces but that adjustable**)


------------------------GENERATE A RANDOM NUMBER------------------------
set nmbr to rndm()
on rndm()
	try
		set {intgrs, oprtrs} to {{1, 2, 3, 4, 5, 6, 7, 8, 9, pi}, {"+", "-", "*", "÷", "^"}}
		set {a, b, c} to {some item of intgrs, some item of intgrs, some item of intgrs}
		set {x, y, z} to {some item of oprtrs, some item of oprtrs, some item of oprtrs}
		set scpt to "  " & a & "     " & x & "    " & b & "   " & y & "   " & c & "  " & z & " " & some item of {a, b, c}
		set nmbr to run script scpt
	on error err ----->When result is too large this error handler will unnecessarily show you the problem.
		display dialog "Oops! " & err & return & scpt with title "skip this attempt!"
		return my rndm()
	end try
end rndm

------------------------CONVERT THE NUMBER TO READABLE TEXT------------------------
set n to do shell script "printf \"%1.2f\" " & (nmbr as text) ---This converts scientific notation to real numbers.**change the '.2' to '.3' for '.xxx', or to '.6' for  '.xxxxxx', etc.
set {strng, thrd} to {"", 0}
repeat with chr in (reverse of (every character of n))
	set {strng, thrd} to {chr & strng, thrd + 1}
	if (length of strng) ≥ (offset of "." in ((reverse of (every character of n)) as text)) then --add commas!
		if (chr as text is ".") then set thrd to 0
		if (length of strng > 2) and ((characters 1 thru 2 of strng) as text) is ".," then set strng to "." & (characters 3 thru -1 of strng)
		if (thrd = 3) then set {strng, thrd} to {"," & strng, 0}
	end if
end repeat
if ((characters 1 thru 2 of strng) as text) is "-," then set strng to "-" & (characters 3 thru -1 of strng) as string -->prevents '-,xxx'
if character 1 of strng is "," then set strng to (characters 2 thru -1 of strng) as string -->prevents ',xxx'
return strng

Model: Mac Pro, Yosemite
AppleScript: 2.7
Browser: Safari 601.2.7
Operating System: macOS 10.14

#15

Hi Mr. Science.

Looking at your code quickly, the two things which spring to mind immediately are that different countries use different decimal point characters and thousands separators and that coercions of lists to text should be done with AppleScript’s text item delimiters explicitly set (to “” in this script, but substring extractions like ((characters 1 thru 2 of strng) as text) should instead be done with (text 1 thru 2 of strng)).

Later: Here’s a first attempt at an ASObjC solution:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

------------------------GENERATE A RANDOM NUMBER------------------------
set nmbr to (random number from -9.9E+24 to 9.9E+24) / ((random number (9.9E+12 - 1)) + 1)


------------------------CONVERT THE NUMBER TO READABLE TEXT------------------------
set decimalPlaces to 2 -- Adjust to taste.

set theFormatter to current application's class "NSNumberFormatter"'s new()
tell theFormatter to setNumberStyle:(current application's NSNumberFormatterDecimalStyle)
tell theFormatter to setMinimumFractionDigits:(decimalPlaces)
tell theFormatter to setMaximumFractionDigits:(decimalPlaces)
tell theFormatter to setLocalizesFormat:(true)
return (theFormatter's stringFromNumber:(nmbr)) as text
#16

Hello Nigel

In fact, the error dialog is issued every time when the value pi is used because when the main string is built, the decimal separator in pi is replaced - here in France - by a comma.

Here is what I got :

tell current application
run script " 7 - 8 ^ 3,14159265359 ^ 3,14159265359"
→ error “« « , » » ne peut pas se trouver après « numéro ».” number -2740
end tell
tell application “Script Editor”
display dialog “Oops! « « , » » ne peut pas se trouver après « numéro ».
7 - 8 ^ 3,14159265359 ^ 3,14159265359” with title “skip this attempt!”
→ {button returned:“OK”}
end tell
tell current application
run script " 3,14159265359 + 9 ^ 1 - 1"
→ error “« « , » » ne peut pas se trouver après « numéro ».” number -2740
end tell
tell application “Script Editor”
display dialog “Oops! « « , » » ne peut pas se trouver après « numéro ».
3,14159265359 + 9 ^ 1 - 1” with title “skip this attempt!”
→ {button returned:“OK”}
end tell

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) samedi 9 février 2019 12:05:04

#17

Thanks, Yvan. So pi needs to be left out of the list of — er — ‘intgrs’. :wink: I’ll see to that immediately. (Edit: In fact I’ve now replaced the random number generation process completely.)

#18

Hello Nigel.

Now, when I get an error, it’s really because the number is too large.

tell current application
run script " 9 ^ 7 ^ 9 + 9"
→ error “Le résultat d’une opération numérique était trop élevé.” number -2702
end tell
tell application “Script Editor”
display dialog “Oops! Le résultat d’une opération numérique était trop élevé.
9 ^ 7 ^ 9 + 9” with title “skip this attempt!”
→ {button returned:“OK”}

When I enter the formula in Numbers, I get : 1310020508637620000000000000000000000000000000000000000000000

In both cases the problem is not the conversion of the number, it’s its creation.

Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) samedi 9 février 2019 12:44:52

#19

What about:


-- Generate a random number (borrowed from Nigel Garvey's reply:
set nmbr to (random number from -9.9E+24 to 9.9E+24) / ((random number (9.9E+12 - 1)) + 1)

-- Return the number in both its original scientific notation
-- and a string representing its full decimal notation
{nmbr, nmbr as yards as string}
#20

It’s a neat trick, but it doesn’t include thousands separators, and it doesn’t give you any control over the number of decimals.