'Convert a text string to number by removing non-numeric characters
Function text2num(ByVal textvalue As String) As Double
Dim newtext As String
Dim n As Integer
Dim c As Integer
For n = 1 To Len(textvalue)
c = Asc(Mid(textvalue, n, 1))
' is it a number (0-9) or a period "." ?
If (c >= 48 And c <= 57) Or c = 46 Then
newtext = newtext & Mid(textvalue, n, 1)
End If
Next n
text2num = Val(newtext)
End Function
DON'T MISS
This formula is working with Profile1, but it's not working with Profile2.
Rating Parameter's can be in number or percentage style. I think your formula only working with percentage style :-)
And same with my reply to Trowa, Parameter Rating can be written inconsistently. For example in cell G5 i wrote it "85<=%" and maybe in the other cell i wrote it ">=85%".
I want to make the formula more flexible with the writings.
I'm sorry to get you confused :-)
Thanks in advance,
Yudi
=IF(I4<=VALUE(LEFT(RIGHT($C$4,4),4)),"1",IF(I4<VALUE(LEFT(RIGHT($D$4,4),4)),"2",IF(I4<VALUE(LEFT(RIGHT($E$4,4),4)),"3",IF(I4<VALUE(LEFT(RIGHT($F$4,4),4)),"4",IF(I4>=VALUE(LEFT(RIGHT($G$4,4),4)),"5","")))))
The ratings need to be changed to the below for it to work:
<=1.50 <2.50 <3.50 <4.50 <=4.50
Thanks for your help.
It works for Profile2, and maybe with a little bit changes, your formulas will work with any other Profiles that I have in my Excel sheet.
Thaks a lot :)
Regards,
Yudi