LinkBack URL
About LinkBacks
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?
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?
Do you mean this?
=SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001
Or, if that won't work, this:
=--SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try this.
=SUBSTITUTE('Worksheet1'!A1;"<";"")-0,00001
If posting code please use code tags, see here.
Thanks that worked but it only needs to do it if its removing the less than sign. In this case it does it regardless
Try this.
=IF(NOT(FIND("<";A1));A1;SUBSTITUTE(A1;"<";"")-0,0001)
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
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?
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
Oops, I actually posted the wrong formula.
Try this.
=IF(ISERROR(FIND("<";Sheet1!A1));Sheet1!A1;SUBSTITUTE(Sheet1!A1;"<";"")-0,0001)
Last edited by Norie; 10-06-2020 at 04:18 AM.
That works, thank you!
Last edited by mss90; 10-06-2020 at 04:40 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks