Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
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 "Barry Bickmore" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
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 -----Original Message----- 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 "Barry Bickmore" wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
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 wrote in message ... 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 -----Original Message----- 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 "Barry Bickmore" wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
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 "Barry Bickmore" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format function question
Not sure if this acknowledges the problem...
MacXL: Custom Scientific Number Format Displayed Incorrectly http://support.microsoft.com/default...Product=XL2001 -- Dana DeLouis Win XP & Office 2003 "Barry Bickmore" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.csv Re-Format Question | Excel Discussion (Misc queries) | |||
Yet Another Name Format Question | Excel Discussion (Misc queries) | |||
how do you write format results of a function within a function? | Excel Worksheet Functions | |||
Format Question | Excel Discussion (Misc queries) | |||
Format % Question | Excel Worksheet Functions |