Lookup specific text in a cell and highlight

Try this for your formatting rule

Formula:

Please Login or Register  to view this content.

Note that this is still not perfect, it looks at the number of characters, but not what the characters are. To validate all of the characters as numeric, you would have to check each character individually which will require some far more complex (and inefficient) formulas.

With conditional formatting, you don't need to test true or false, any non zero number will evaluate to true. Zero, text or errors will default to false.

@jason.b75 thanks for your reply but your formula results in only A6 & A7 highlighting??

Sorry, missing $ symbols

Formula:

Please Login or Register  to view this content.

Thanks, fixed the problem, however.

Formula highlights row if one or the other is correct. So if one reference is incorrect it still highlights row, if that makes sense.

I think that this should work for incorrect TCN references,

Formula:

Please Login or Register  to view this content.

But not sure about A references,

Formula:

Please Login or Register  to view this content.

If, as per your sample, it will always be both refernces with no other text, or just an A reference at the end of a cell with other text then I can probably make it a bit more reliable, if this is not the case then I think there will always be exceptions that can slip through.

Those formulas aren't working for me, highlights entire selected range??

It will always be A reference and TCN reference, or just A reference. Could contain other text but needs to check for these correct references to flag up errors.

Another option if possible could be to look up more/less than 10 or 5 digits and highlight the error rather than a correct entry, this way it might be possible to use separate formulas for A ref and TCN ref that highlights a non match??

As I said previously, it is not an easy task to identify digits in a cell with mixed data. You can only identify numbers as numbers if there is no other text in the cell.

Using 2 separate rules will not work, that would create an error on row 8 as there is no TCN.

I'll give it some more thought, but I think that you might need vba to make this work.

Thanks for your patience, I initially thought there would be a way to look up a specific number of characters but I see its not an easy one.

I was thinking it might be a case of VBA but figured I'd try here first.

So just thinking about the way I described this I may have confused things slightly when talking about specific numbers.

Let's try this, is there a formula that can be used to highlight if the number of digits after the 'A' & 'TCN' doesn't equal 10 & 5 respectively?

Thanks

Thanks for your reply, seems to have a lot of calculations in this formula to make it work. Great job.

Yeah it went a tad shouty.

Currently have it working through VBA but always nice to see how things can work. I see it only looks up the amount of numbers not specifically the preceding A or TCN so I'll put it in to my workbook and have a play with it.

Thanks again.

Tez

Yeah I'd be interested in modified formula, currently having problems with macro's and protected sheets so less macro's the better lol