Convert data with a formula

[Diane Cuthbertson]

I have a column of data that is mixed. Some of the data is entered with a < symbol. Like <0.25

I have tried an IF to no avail (I am sure it is dumb user error). Anyway, all I want the formula to do is look at the cell and if it contains any entry that has a "<" symbol with a number to convert it to a number that would be just under the number stated. SO if it says <0.25 to convert it to 0.249. I hopes this makes sense. I have over 2000 rows of data entered like this.

Thanks in advance.

 

[NARAYANK991]

Hi Diane ,

Try this :

=SUBSTITUTE(A1,"<","")+IF(ISNUMBER(SEARCH("<",A1)),-0.00001,0)

where A1 has your data.

Narayan

 

[Khalid NGO]

Hi,
You can also try:
=IF(ISTEXT(A1),SUBSTITUTE(A1,"<","")-0.001,A1)

Regards,

 

[Somendra Misra]

Hi,

Another option:

=IF(ISNUMBER(SEARCH("<",G8)),(--SUBSTITUTE(G8,"<",""))-0.001,G8-0.001)

G8 is the cell with number.

Regards,

 

[Diane Cuthbertson]

Hi Diane ,

Try this :

=SUBSTITUTE(A1,"<","")+IF(ISNUMBER(SEARCH("<",A1)),-0.00001,0)

where A1 has your data.

Narayan

You ROCK!