Help Requested: custom number format code, working with <, > signs

Hey Guys and Gals,

I'm importing lots of data from lab results on to spreadsheets. I want to standardize data that's been imported by different people over many years. I would like only one zero to be displayed if there are no other numbers in the tenth's/hundredth's place. The issue is, I believe, that most of the data has a < (less than) sign in front of the number, signifying that it was below the detection threshold. This "<" doesn't allow me to simply use the "move decimal point" button.

Could anyone help me with writing a custom number format code that would display only one zero to the right of the decimal point in the tenth's place but only if there are no other numbers present.

TLDR: I have many <1.00, <3.00, and <5.00s. Want to make them <1.0, <3.0, <5.0 while preserving data such as 1.98, 0.56, etc...

Thanks!

Perhaps a macro like this could work?

Please Login or Register  to view this content.

You need to set the propper range of cource. Macro checks if cell value starts with a "<" and that the last digit is "0" if so then the last "0" will be removed.

So a value like "<1.50" will be changed to "<1.5" but a value like "<1.45" will not be changed as the last digit is not "0" nor will the normal numbers i.e. 1.98, 0.56 ...

Alf

Thanks for the reply, Alf. You've pointed me in the right direction, but I think I need something a bit more specific/complicated. My data includes numbers such as "<1" , "<10" , and "<1.0". I ran the macro, and it's changing the "<10" to "<1" and the "<1.0" to "<1.". I would like all data ending in zero to display a zero in the tenth's place but no further and no less. "<1" goes to "<1.0", "<10" goes to "<10.0", "<1.0" remains as is, etc, all while leaving numbers like "1.58" alone.

Is it feasible to write a macro like this, or is this becoming too complicated?

Thanks for the help!

Well the first macro was based on you examples and you have added some new conditions. Can you think of more formats that should be taken in account?

If these are lab results I would not be surprised if you have values like <100 or even <1000 depending on if measurements are made on the ppb level.

How about following rules:

If no < found in 1st position then do nothing

If cell value contain “<” but no “.” then do nothing

If cell value contain “<” and “.” then remove last digit if it is “0”

I'm a bit unsure about "<1" and "<10" being changed to "<1.0" and "<10.0" because that would in my opinion indicate a higher degree of accuracy than originally given in the analysed result.

Alf

Modified according to your last request so test and see if this was what you wanted.

Please Login or Register  to view this content.

Alf