How to divide a number with "less than" sign? ie: <1/1000 = <0.001

[justinbeau]

Need to due a number of conversions from ug --> mg in a column. Many of the
numbers that I need to convert have a "less than" sign... ie <1 should
convert to <0.001

 

[Dave Peterson]

If that's the only change, you could select the range to fix:

Edit|replace
what: <
with: <0.00
replace all

or
what: <1
with: <0.001
replace all

Or a bunch of combinations like this.

 

[Shane Devenshire]

Hi,

A number with a < sign is not a number in Excel, it is text. and if you are
trying to convert it as in your title the result will not be a number either.
To do what you subject line asks probably would require a VBA macro.

Here is a macro you could run against a select range of cell to convert them
(only if the leading character is <.

Sub ConvertIt()
Dim cell As Range
Dim x As Integer
Dim First As Double
Dim Second As Double
For Each cell In Selection
If Left(cell, 1) = "<" Then
x = InStr(1, cell, "/")
First = Mid(cell, 2, 1)
Second = Mid(cell, x + 1, 10)
cell = "<" & First / Second
End If
Next cell
End Sub

 

[David Biddulph]

=IF(LEFT(A2)="<","<"&RIGHT(A2,LEN(A2)-1)/1000,A2/1000)