# Remove sign and subtract in the same formula

1. ## Remove sign and subtract in the same formula

I use the following formula "=SUBSTITUTE('Worksheet1'!A1;"<";"")" to remove the less than sign from my data. Is it possible to add in subtract 0,00001 as well from the values after < has been removed?

2. ## Re: Remove sign and subtract in the same formula

Do you mean this?

=SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001

Or, if that won't work, this:

=--SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001

3. ## Re: Remove sign and subtract in the same formula

Try this.

=SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001

4. ## Re: Remove sign and subtract in the same formula

Thanks that worked but it only needs to do it if its removing the less than sign. In this case it does it regardless

5. ## Re: Remove sign and subtract in the same formula

Try this.

=IF(NOT(FIND("<";A1));A1;SUBSTITUTE(A1;"<";"")-0,0001)

6. ## Re: Remove sign and subtract in the same formula

But now if the value doesnt have the less than sign I only get #value. So I would like the function to paste inn the value of it doesnt have <, if it has it I would like it to remove the < sign and subtract 0,00001 from it

7. ## Re: Remove sign and subtract in the same formula

Strange, when I try the formula with values that don't have the '<' it returns the value.

Can you attach a workbook with sample data?

8. ## Re: Remove sign and subtract in the same formula

So I add your formula but take the data from a different sheet "=IF(NOT(FIND("<";Sheet1!A1));Sheet1!A1;SUBSTITUTE(Sheet1!A1;"<";"")-0,0001)".
Please see attached excel doc for example

9. ## Re: Remove sign and subtract in the same formula

Oops, I actually posted the wrong formula.

Try this.

=IF(ISERROR(FIND("<";Sheet1!A1));Sheet1!A1;SUBSTITUTE(Sheet1!A1;"<";"")-0,0001)

10. ## Re: Remove sign and subtract in the same formula

That works, thank you!

There are currently 1 users browsing this thread. (0 members and 1 guests)