VBA Scientific Format. Can I reduce a digit?

[aerodoc]

This bit of code works for me. But the problem is that I am passing the data to a program that can only be 8 characters long. With this format (and assuming a positive number) I only get 3 significant digits out of 8. The good news is the code I am passing it to does not need the "E" part so I can get 4 significant digits if I can ask EXCEL to remove the E when writing the line to a text file. Is there a simple way to adjust this?


Format(Cells(1,1), "0.00E+00")

[SamT]

Dim FourthSignificantDigit As String
NewString = substitute(Cells(1, 1).Text, "E" ,FourthSignificanDigit,)

[aerodoc]

Thanks. I did have a problem with the substitute command (running 2003 for compatibility). Perhaps that was added later? I used this instead.


NewString = Replace(OrigString, "E", "")

[snb]

why not


left(format(cells(1)),8)

[SamT]

I think he is tryling to convert Excel scientific notation to other S.N.
0,000+00

[Paul_Hossler]

Would just changing the Format () string work?




Option Explicit
Sub NewSciFormatTest()
Dim N As Double
Dim S As String

N = 1234.5678
S = Format(N, "0.000E+00")
MsgBox S

S = Left(S, 5) & Right(S, 3)
MsgBox S
MsgBox Len(S)

N = 123412341234.568
S = Format(N, "0.000E+00")
MsgBox S

S = Left(S, 5) & Right(S, 3)
MsgBox S
MsgBox Len(S)

End Sub



Paul

[aerodoc]

Paul,

That is a pretty good idea too and I was just about to try it. But I think the presence of a negative sign makes the position of the E unpredictable. Sure, some code could be added to address it, but by that point, it would be too much effort.