Determine if last character meets condition

[Celticshadow]

Hello All

May I ask the board for help with a formula to find the last number in a cell and if that number meets a condition put a letter in the adjacent cell.

In cell A1 I have numbers as such 254671 (these numbers vary in length but not normally more than 8 in length). I want to know if the last numeral on the right is a 1 and if so then enter in the adjacent cell (B1) the letter y if not then leave blank.

Thanks in advance.

Regards

 

[JB_Scotland]

In cell B1 enter the formula =IF(VALUE(RIGHT(A1,1))=1,"y","")

 

[Celticshadow]

Hello JB Scotland

Many thanks for replying. When I enter your formula it returns the #value, will that be due to some unforeseen formatting?

Regards

 

[JB_Scotland]

You will get #VALUE if A1 is blank or contains text with no number at the right end. If you want to avoid the #VALUE error use this:

=IF(RIGHT(A1,1)="1","y","")

 

[Celticshadow]

ADVERTISEMENT

Hello JB Scotland

That works an absolute treat and many thanks for your help it is much appreciated.

Regards

 

[Rick Rothstein]

Here are two more ways in which to do it...

=LEFT("Y",RIGHT(A1)="1")

=MID("Y",1,RIGHT(A1)="1")

 

[JB_Scotland]

Rick - neat.

In the same vein if you like your RIGHTS:

=RIGHT("Y",RIGHT(A1)="1")