Need a value to be in exponential format and then converted to a text with a symbol in the front.

[HankJ]

Greetings

I use the following notation to report certain values.

>1.000
<0.0001

These values are in units of micromolar.
I would like some code to convert them to the following.

>1.00E-06
<1.00E-10

I have something like this..

Sub Tidy_IC50()

' Tidy IC50 value if >1.000000
Dim dData As Range, aCell As Range

    Set dData = Range("M5:M9")                 ' s

    For Each aCell In dData.Cells                       '  starts loop in Range aData
        If Left(aCell, 1) = ">" Then
        aCell = Mid(aCell, 2, 10) / 1000000#
        aCell = ">" & aCell
        Else
        aCell = aCell
        End If
    Next

End Sub

Unfortunately is gives me:
[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]>0.0000001[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be gratefully received.

Best wishes

Hj

 

[Momentman]

Your code assumes that if the cell does not have a greater or less than sign, it should remain the same?

Well, i modified a portion of it

Sub Tidy_IC50()


' Tidy IC50 value if >1.000000
Dim dData As Range, aCell As Range


    Set dData = Range("A1:A2")                 ' s


    For Each aCell In dData.Cells                       '  starts loop in Range aData
        If Left(aCell, 1) = ">" Or Left(aCell, 1) = "<" Then
        aCell = Mid(aCell, 2, 10) / 1000000
        aCell = ">" & Format(aCell, "0.E+00")
        Else
        aCell = aCell
        End If
    Next


End Sub

 

[HankJ]

Hi Momentman
That worked an absolute treat.
I really need to work on my formats and this will be a good one to save for the next.

Yes, if the value is inside the limits, then they stay as they are.

Many thanks for your time and knowledge.
Wish you good.

Hj