Indirect (combined with mid/trim) is not returning a numerical value

[markpgates]

Hi all, quickly losing my mind.

Via the forum, I've got a grip on indirect (using sheet names) to return a value based on a dynamic cell (as opposed to static).
I have then combined this with MID and TRIM to return the number portion of a text string in said sheet name as above....unbelievably that works.

For reference the formula is correctly returning the number portion of a text string from another sheet;

=TRIM(MID(INDIRECT("'"&E6&"'!"&"K15"),6,2))

The cell in K15 of the sheet (as named in E6) contains the phrase "Step 17 - Transition"
The formula returns 17 - Great!

The problem I am facing is I want to use conditional formatting on the portion that is returned (17), however I can't get it to work, and it looks like conditional formatting isn't recognizing it as a value (checked formatting of cells etc)

If however in a blank cell I type =1+ (the returned text), excel accepts it is a number, and gives me the answer of 18

I have no idea what to do, any assistance very much appreciated! Happy to send file if anyone can help!

Thanks all

 

[jasonb75]

If however in a blank cell I type =1+ (the returned text), excel accepts it is a number, and gives me the answer of 18

Use +0 instead to keep the original value.

 

[Joe4]

The MID function will always return a text value, even if it returns all numbers.
By applying some mathematical operation to it, like adding 1, it coerces those numbers returned as text to be numbers.
If you wanted to keep the same value, either add zero or multiply by one.

 

[markpgates]

Use +0 instead to keep the original value.

The phrase hero gets thrown around these days, however in this instance....!!

Thank you @jasonb75 you legend.