Originally posted by: SystemAdmin

I was asked how to do this today, and I decided to share the result on this forum:

code:1:3563cc54da
=IF(MID(In1,FIND("E",In1)1,1)="",TONUMBER(LEFT(In1,FIND("E",In1)-1))*TONUMBER("1"+SERIESTOTEXT(CLONE("0",TONUMBER(MID(In1,FIND("E",In1)+2,999)))),"1"),TONUMBER(LEFT(In1,FIND("E",In1)-1))/TONUMBER("1"+SERIESTOTEXT(CLONE("0",TONUMBER(MID(In1,FIND("E",In1)+2,999)))),"1"))
[/code:1:3563cc54da]

This rule assumes that the item 'In1' contains a single piece of text. It works with decimals (1.4E-5) too.

Enjoy.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Nice code, Paul. I'll tuck it away in my Mercator Tricks directory and hope I never have to worry about scientific notation in maps.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Here's a variant where the number may or may not be in e notation and positive E numbers do not contain a +. Value in In2.

=if(present(word(In2,"E",2)),
if(tonumber(word(In2,"E",2)) > 0,
tonumber(word(In2,"E",1)) * (tonumber("1" + seriestotext(clone("0",tonumber(word(In2,"E",2)))))),
tonumber(word(In2,"E",1)) / (tonumber("1" + seriestotext(clone("0",tonumber(word(In2,"E",2))))))),
tonumber(In2))

So this would deal with

1.27E+11
1.27E11
1.27E-11
127000000000
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender