using format to output only specific scientific notations? (milli,micro)

[chupacabra]

Hi,

I need to output numbers in string format, but only in e+3, e-3, e+6, e-6 formats. NO e-4 or anything in between, only 3/6/9/12.

Is there a way to do this? I've been racking my brain, but everything I have tried has not really worked very well (at all).

Thanks!

[AM003295]

what does the numbers look like before you have to output it?

[chupacabra]

Oh, forgot to mention that.

It varies slightly.
On the left is how it is passed in, on the right now I want it displayed:
0.3215689 321.569e-3
1.4642 1.464
0.003568 3.568e-3
0.000123456 123.456e-6
0.000002684 2.685e-6

etc etc

[AM003295]

Looks like you might have to do it the hard way, can't find an easy way. I'll work on it a little and see what I get.

[AM003295]

This is the hard way. Copy this function in your code and pass it the number.


Public Function NumConversion(sStartNum As String) As String
Dim sDPoint As String
Dim sa As String, sb As String, sc As String
Dim i As Integer, j As Integer, k As Integer, l As Integer
j = 0 'j is the number of "0"'s counter
sDPoint = "."

k = Len(sStartNum) 'get the total length of the string
l = InStr(sStartNum, sDPoint) 'find the location of the decimal point

For i = l To k 'find out how many "0" you have after the decimal point
If Mid(sStartNum, i, 1) = "0" Then 'see if the number after the decimal point is a "0"
j = j + 1 'incrment the (num of "0" after the decimal point) counter
End If
Next
If j = 0 Then GoTo Tag1 'then no expoinent is required
If j = 1 Then 'now get the digits and insert the dec point and expoinent
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the first "0"
NumConversion = Left(sa, j + 1) & "." & Mid(sa, j + 1, Len(sa)) & "e-3" 're-construct the number with a dec point and exponent.
'take the left 3 digits after removing the one 0
'add a dec point
'add the remaining digits
ElseIf j = 2 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the second "0"
NumConversion = Left(sa, 1) & "." & Mid(sa, 2, Len(sa) - 1) & "e-3" 're-construct the number with a dec point and exponent.
'take the left 1 digits after removing the two 0's
'add a dec point
'add the remaining digits
ElseIf j = 3 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the third "0"
NumConversion = Left(sa, 3) & "." & Mid(sa, 4, Len(sa) - 3) & "e-6" 're-construct the number with a dec point and exponent.
'take the left 3 digits after removing the three 0's
'add a dec point
'add the remaining digits
ElseIf j = 4 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 2) & "." & Mid(sa, 3, Len(sa) - 2) & "e-6" 're-construct the number with a dec point and exponent.
'take the left 2 digits after removing the four 0's
'add a dec point
'add the remaining digits
ElseIf j = 5 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 1) & "." & Mid(sa, 2, Len(sa) - 1) & "e-6" 're-construct the number with a dec point and exponent.
'take the left 1 digits after removing the five 0's
'add a dec point
'add the remaining digits
ElseIf j = 6 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 3) & "." & Mid(sa, 4, Len(sa) - 3) & "e-9" 're-construct the number with a dec point and exponent.
'take the left 3 digits after removing the three 0's
'add a dec point
'add the remaining digits
ElseIf j = 7 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 2) & "." & Mid(sa, 3, Len(sa) - 2) & "e-9" 're-construct the number with a dec point and exponent.
'take the left 2 digits after removing the four 0's
'add a dec point
'add the remaining digits
ElseIf j = 8 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 1) & "." & Mid(sa, 2, Len(sa) - 1) & "e-9" 're-construct the number with a dec point and exponent.
'take the left 1 digits after removing the five 0's
'add a dec point
'add the remaining digits
ElseIf j = 9 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 3) & "." & Mid(sa, 4, Len(sa) - 3) & "e-12" 're-construct the number with a dec point and exponent.
'take the left 3 digits after removing the three 0's
'add a dec point
'add the remaining digits
ElseIf j = 10 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 2) & "." & Mid(sa, 3, Len(sa) - 2) & "e-12" 're-construct the number with a dec point and exponent.
'take the left 2 digits after removing the four 0's
'add a dec point
'add the remaining digits
ElseIf j = 11 Then
sa = Mid(sStartNum, (l + j + 1), k - (l + j)) 'get all the numbers after the "0"'s
NumConversion = Left(sa, 1) & "." & Mid(sa, 2, Len(sa) - 1) & "e-12" 're-construct the number with a dec point and exponent.
'take the left 1 digits after removing the five 0's
'add a dec point
'add the remaining digits
End If
Exit Function
Tag1: DoEvents 'this means you had no "0" after the decimal point so treat it as not requireing an "e"
NumConversion = CStr(Round(Val(sStartNum), 3)) 'but round it off to 3 places and change it back to a string
End Function