Text to number in conditional formatting

I

Igor G.

I have problem with conversion text to number and use this in conditional
formatting.
Example:
I have cell (text field) with value: <300
How to convert this value to number 300 and use in conditional formatting?

Thanks!
 
B

Brendan Reynolds

You'll need an expression something like this ...

=IIf(Left$([TestText],1) In
(">","<"),Val(Mid$([TestText],2)),Val([TestText]))

.... where 'TestText' is the name of your field.

Use this expression in the control source property of a text box, but make
sure that the text box does not have the same name as the field.

If your data can begin with non-numeric characters other than ">" and "<",
add them to the list.
 
K

Khoa

The following function will extract only number from a text string.
'----------------------------
Public Function ExtNumber(mText As String)
Dim i As Long, LenT As Long, t As String
ExtNumber = ""
LenT = Len(mText)
For i = 1 To LenT
t = Mid(mText, i, 1)
If InStr("0123456789", t) > 0 Then
ExtNumber = ExtNumber & t
End If
Next i
End Function
'------------------------
Note that the above function returns numbers as string, e.g:
ExtNumber("<>=//abcd02A+<045") returns 02045
If you want the result as number, add Val function, e.g:
Val(ExtNumber("("<>=//abcd02A+<045"))
or you may add : ExtNumber = Val(ExtNumber) at the end of the above function.
Good luck.
 
Top