Conditional formula - dealing with < numbers as numeric fields

[GulfLass]

We have a set of data from a laboratory containing <values that we want to
prepare for importing to Access. We need to ensure that Access recognises the
values as numeric (for later graphing & calculations) but cannot simply
remove the < symbol - rather we want to remove the < symbol and multiply the
value by e.g. 0.99999 (to differentiate from values that were actually 'x'
not '<x'.

e.g. have

Sample ID, Parameter 1, Parameter 2,
abc, <0.001, 0.01

want

Sample ID, Parameter 1, Parameter 2
abc, 0.000999999, 0.01

Any help would be appreciated (maybe this can be done at the Access import
stage?).

 

[Pete_UK]

Try this formula in D2:

=IF(LEFT(B2)="<",RIGHT(B2,LEN(B2)-1)*0.99999,B2)

Then you can copy this down as far as you need to. If you fix the
values from this formula, you could then copy/paste over the original
values and then you could delete column D.

Hope this helps.

Pete