convert string with a decimal to a string with a exponential

[HankJ]

Hello

I would like to convert a string with a decimal value >100.00000, which are in units of µM to a string with an exponential value, > 1.00E-04.

For example:

[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
[TR]
[TD]>100.000000[/TD]
[/TR]
</tbody>[/TABLE]

to

[TABLE="width: 90"]
<colgroup><col></colgroup><tbody>[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04
[/TD]
[/TR]
[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04[/TD]
[/TR]
[TR]
[TD]> 1.00E-04
[/TD]
[/TR]
</tbody>[/TABLE]

the bit of code I am using changes everything to >0.0001!

If Left(Selection.Value, 1) = ">" Then
Selection.Offset(0, -1).FormulaR1C1 = _
"=MID(RC[1],2,SEARCH(CHAR(127),SUBSTITUTE(RC16,""."",CHAR(127),1))-2)/1000000"
Selection.Offset(0, 2).Value = Selection.Offset(0, -1).Value
Selection.Offset(0, 2).NumberFormat = "0.00E+00"
ConcVal = Selection.Offset(0, 2).Value
Selection.Offset(0, 2).Value = "> " & ConcVal
Selection.Offset(0, -1).Value = ""
Selection.Value = ""

This was an attempt to force the value to be a string but does not quite work.

I would be grateful for suggestions.

Many thanks

Hankj

 

[Fluff]

Try

ConcVal = Format(Selection.Offset(0, 2).Value, "0.00E+00")

 

[HankJ]

Try

ConcVal = Format(Selection.Offset(0, 2).Value, "0.00E+00")

Hi Fluff,

Worked a treat.

Many thanks for your time and help

Hank

 

[Fluff]

Glad to help & thanks for the feedback