Format function question

[Barry Bickmore]

Hello, I am programming a series of Excel macros where the end result is to
write data from the cells into a text file. I am using the Print # command to
do this. One problem I have is that I need to put some of the numbers into
scientific notation in the text file, but the Format function does not seem to
work for me. For instance, suppose I had a worksheet cell where the value
is 0.002. I would write a macro line like this:

Print #1, Format(insert reference to cell here, "0.00E-00")

In the text file, this produces a value of 0.00E-00. If the cell value is 0.1, I
get 0.10E-00.

In other words, it seems like the Format function is putting the numbers in
the right format, but it keeps the exponent term equal to 0 and truncates
after 2 decimal places.

PLEASE HELP!!! In case it matters, I'm working on a Macintosh.

Thansk!

Barry

 

[keepITcool]

on winXP
my regional settings use a comma as decimal separator.

the format function produces "localized strings"
but probably for your csv you'll need US english notation.


in the immediate pane:
?format(-1.234567,"0.0000E+00")
-1,2346E+00
?format(-0.02,"0.00E+00")
-2,00E-02

?replace(format(-1.234567,"0.0000E+
00"),application.DecimalSeparator,".")
-1.2346E+00
and
?replace(format(-0.02,"0.00E+00"),application.DecimalSeparator,".")
-2.00E-02

could that be your problem?

are you testing your csv file with excel
of with a text editor ?



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Guest]

Hi,

Thanks for the thought, but nope, I'm using US English settings. When I use
the Immediate pane and type:

?format(0.0000432, "0.00E-00")

The answer I get back is:

0.00E00

Any other thoughts?

Thanks!

Barry

 

[Dana DeLouis]

It worked ok for me with Excel 2003. Same answer w/ either + or - in the
format.

?format(0.0000432, "0.00E-00")
4.32E-05

?Format(0.0000432, "0.00E+00")
4.32E-05

--
Dana DeLouis
Win XP & Office 2003


Hi,

Thanks for the thought, but nope, I'm using US English settings. When I use
the Immediate pane and type:

?format(0.0000432, "0.00E-00")

The answer I get back is:

0.00E00

Any other thoughts?

Thanks!

Barry

 

[Barry Bickmore]

I tried it on a PC, as well, and it worked. Looks like it must be a bug in the
Mac versions. (I tried it on both Excel X and Excel 2004 for Mac.) Too bad I
have my Office software on a site license, and so the rats at Microsoft want
to charge me $30 just so I can report the bug.

Thanks!

Barry

 

[keepITcool]

aha... there's always workarounds...

try following...

Function FormatE(number As Double) As String
FormatE = Application.WorksheetFunction.Text(number, "0.000E+00")
End Function


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dana DeLouis]

Not sure if this acknowledges the problem...

MacXL: Custom Scientific Number Format Displayed Incorrectly
http://support.microsoft.com/default.aspx?scid=kb;en-us;190807&Product=XL2001

--
Dana DeLouis
Win XP & Office 2003


I tried it on a PC, as well, and it worked. Looks like it must be a bug in
the
Mac versions. (I tried it on both Excel X and Excel 2004 for Mac.) Too bad
I
have my Office software on a site license, and so the rats at Microsoft want
to charge me $30 just so I can report the bug.

Thanks!

Barry