Conditional Formatting doesn't work with formula result

Hi all I am having problems with conditional formating. What I want is to have 2 tabs showing number ranges in the form of the start and end of the sequence (i.e. 10 codes from 10990 to 10999). T...

excel

Formulas and Functions

mtarler
Dec 09, 2022
quick answer if I'm pretty sure your problem is the 'shortened' result is TEXT and not a number. Simply add = --( original formula ) to force excel to convert it into a number or use VALUE( orig formula). So in your example it would be:
=--(LEFT(E12,4)&RIGHT(E12,LEN(E12)-6))

FYI - another thought (not related to the actual problem) is you could use SUBSTITUTE() to replace "-0" with "" to remove those 2 characters instead of getting LEFT and RIGHT and putting them back together so again in your example it would be:
=--SUBSTITUTE(E12,"-0","")

mtarler

Silver Contributor

Dec 09, 2022

quick answer if I'm pretty sure your problem is the 'shortened' result is TEXT and not a number. Simply add = --( original formula ) to force excel to convert it into a number or use VALUE( orig formula). So in your example it would be:

=--(LEFT(E12,4)&RIGHT(E12,LEN(E12)-6))

FYI - another thought (not related to the actual problem) is you could use SUBSTITUTE() to replace "-0" with "" to remove those 2 characters instead of getting LEFT and RIGHT and putting them back together so again in your example it would be:

=--SUBSTITUTE(E12,"-0","")

goblinmilk

Copper Contributor

Dec 09, 2022

my god I could kiss you. It works perfectly now. I knew it would be something daft I overlooked and I knew it was something to do with how I got those "shortened" results but I just couldnt see it.

Thank you this has saved my sanity

Forum Discussion

Conditional Formatting doesn't work with formula result

Resources