My day job is as a building scientist, I help people understand the impact that the built environment has on their health, well-being and productivity through data driven consulting. This means that I often conduct environmental quality testing to establish concentrations of pollutants in the air, in water, in materials or on surfaces.

Without going into too much detail, when a result is obtained from our in-house lab or an external lab often the result is below the detection limit of the method. This is often because in analytical chemistry, there is what is called a limit of detection (LOD) which means that below that limit we cannot reliably measure the analyte. When the result is below the LOD it is usually reported in the lab results as < and a number. For example, lets say I measure the amount of Ozone in the air and the lab returns a result of <0.1 ppm, this means that the result was below the detection limit of the method which in this case was likely 0.1 ppm.

The issue with this is that when you want to perform calculations (say in excel), the program will see the < symbol and convert the value to text which means it cannot be used as a calculation. The calculation might be fairly simple (like say we want to convert PPM to PPB which requires us to multiply by a thousand ) but as soon as we try, we will get the following error: #VALUE!

So to work around this, usually I use an IF statement with some search parameters. In this example I am assuming that the value we are looking at is in cell A2, and that the < symbol is the first symbol to appear in the string. I have not included any error handling, and not included any TRIM functions.

How to determine if a string contains a character?

To work out if a string contains a character, the easiest way (IMHO) is to use the ISNUMBER function with a search query. The way this works is that the ISNUMBER returns a true if the string it is being fed contains a number, or false if it does not. The search string looks for the phrase you are searching for, and if it is found it returns the number of characters it started at.

If we want to determine if the “<” symbol appears in the cell A2, we can use the following;

=SEARCH(“<“,A2)

In the case of the value <0.1 in the cell A2, it would return the value 1 as that string starts at position 1. Now if we want to combine this with a true / false statement, we can then use the IsNumber() function, where we include the above search in the brackets like the below;

=ISNUMBER(SEARCH(“<“,A2))

In this case, with the value <0.1 we get TRUE when the string contains < but if you remove that it will change to FALSE.

How to use an IF statement?

With the above, we have determined that we can calculate if the string contains a specific character (in this case <) and return a true or false value but for that to be useful we need to be able to take an action based on if the value is TRUE or FALSE which is where an if statement comes into the picture. In excel the if statement syntax is as per the below;

=IF(Query_to_Run, Value_if_true, Value_if_false)

The query can be a calculation like if A2=”YES”, a formula like A2<100 or even a function like we discussed above. In the case of the above the base query would be;

=IF(ISNUMBER(SEARCH(“<“,A2)), value_if_true, value_if_false)

Now with the above example, if the value is false we can easily run our calculation as it means there is no < symbol in the A2 cell, so value_if_false we could change to A2*1000 but we still need to work on value_if_true which requires us to trim away the < symbol, run the calculation then put it back at the end.

How do we trim the first character?

There are likely a few ways you can remove the first character from the string, but in most cases I tend to use the RIGHT function. This function is designed to remove a specified number of characters from the end of the string, so lets say we have the value “<0.1”, in this case we want to use RIGHT to take the first three characters in the cell when reading from right to left, so we would be left with 0.1. If we were hard coding this we could use

=RIGHT(A2,3)

The issue with the above approach is that as soon as the length of the string changes, it will break so the better approach is to include LEN in the calculation, this takes into account the length of the string in the cell A2, and we can then subtract 1 from the overall length to return the string without the first character;

=RIGHT(A2, LEN(A2)-1)

How does this all fit into the overall IF statement?

So far we have run a formula to determine if the value contains a < symbol, we have fed this into an IF statement and now we need to add the above information to finalize the if statement and to run our multiplication. We addressed the value_if_false which was a simple A2 * 1000, but we now need to include the above RIGHT(A2, LEN(A2)-1) and multiply by 1000 like the below;

=IF(ISNUMBER(SEARCH(“<“,A2)), RIGHT(A2, LEN(A2)-1) * 1000, A2 * 1000)

With the above calculation, you will notice that it is returning 100 irrespective of if the value we start off with in cell A2 is <0.1, or is 0.1 which is scientifically not correct as we need to make sure that we have the < symbol showing in the end result, so to do this I amend the above calculation to the below;

=IF(ISNUMBER(SEARCH(“<“,A2)), “<” & RIGHT(A2, LEN(A2)-1) * 1000, A2 * 1000)

Tip: What if my number shows as a huge number of decimal places?

Often if you are running complex calculations, the value that is returned will be some horrible string like <0.2312432453241343215456423 which is clearly not scientifically correct. In this case you may want to add a ROUND section to the IF statement. The round function works per the below;

=ROUND(number_to_round, number_of_digits_to_round)

Round requires a number to be able to round, so you will not be able to add it anywhere within the IF statement, it needs to be on the number calculation as per the below;

=IF(ISNUMBER(SEARCH(“<“,A2)), “<” & ROUND(RIGHT(A2, LEN(A2)-1) * 1000,1), A2 * 1000)

In the above example, it is rounding the number obtained by the calculation RIGHT(A2, LEN(A2)-1) * 1000 so that only 1 decimal place is showing