' any consequences of the use of this code'ĭim sExpon As String 'exponent as a stringĭim e As String 'exponent in Unicode (superscript) charactersĭim lPwr As Long 'Rounded power of ten of the number (used to determine the exponent)ĭim dVal As Double 'Value of the number as a Double typeĭim Mant As String 'Mantissa for the number in scientific notation ' as is and no liability will be accepted by the programmer for
Custom formatting excel for double quotes for free#
' This code is available for free use at your own risk. ' This is to prevent effects of rounding errors ' LIMITATION: The mantissa of the number is rounded to maximum 13 decimals ' If the output string contains more decimals than desired, you can use the Round() function to reduce the ' If you want any number to be in scientific notation (except 0), set MinPosExp to -1 or MaxNegExp to 1 ' The default range excludes only the numbers that would result in an exponent of 0 ' MinPosExp must normally be a positive, and MaxNegExp a negative integer ' The optional parameters set the range of values that will not be converted to scientific notation ' unicode characters are used that are already in superscript format ' As it is impossible to set the characters to superscript in the function, ' where ''' stands for the exponent, written as a superscript
' the number in scientific notation: xxx♱0''' ' This function converts a number to a string that contains Suggestions for further improvements are very welcome.įunction SciNot(Val As Variant, Optional MinPosExp As Integer = 0, Optional MaxNegExp As Integer = 0) As String I just improved it a bit with Phillip's idea of optional parameters and Choose(). This has worked for me for all practical purposes. Is that only for me, or do others have the same problems?Īnyway, I decided to present my old userfunction in full. Only I get some strange results with negative exponents showing for example as: 0.02322×-10² instead of 2.322♱0⁻² and exponents larger than 9 do not show up at all. Interesting solution from Phillip, nice with the optional parameter and the Choose() function. Create it as a user-defined function and you could then use it in your formulas. If you needed to do quite a bit of formatting in this manner, it would be a relatively trivial matter to create a macro that returned the formatted text string based on the number. The result is considered a text string by Excel it cannot be used in subsequent calculations.
This formula essentially pulls the left portion of the number (the part before the E) and combines it with the right part of the number (the part after the E) together with the "x10^" notation. For instance, if a value that uses Excel's scientific notation is stored in cell C7, you could use the following formula: The only workaround is to use a formula to put together a text representation of what you want. There is no way in Excel to change the way in which scientific notation is displayed. He would like the scientific notation to be shown differently, such as 1.23x10^3 or 1.23x10^-3. Reid notes that he can display numbers using scientific notation and they appear in Excel in the format 1.23E+03 or 1.23E-03.
0 kommentar(er)
