CODEWITHSUNDEEP
stringexcelif-statementexcel-formulaworksheet-function
Sir_Lagalot
Sir_Lagalot

Reputation: 23

IF function to identify first character of cell

I'm trying to use the IF function in Excel so that if the first character of a cell is 9, then the value shown should be the eight rightmost characters of that cell, otherwise the value shown should be the four rightmost characters. This formula however does not work:

=IF(LEFT(A2,1)=9,RIGHT(A2,8),RIGHT(A2,4))

It keeps returning the rightmost four numbers even though the number in cell A2 starts with 9.

Could you please point out what I'm doing wrong here?

Upvotes: 2

Views: 45568

Answers (3)

pnuts
pnuts

Reputation: 59485

A shorter version if the condition is used to set the number of characters: 

=RIGHT(A2,4+4*(LEFT(A2)="9"))

Upvotes: 0

CaringDev
CaringDev

Reputation: 8551

LEFTreturns text, so the comparison needs to also be against a string:

=IF(LEFT(A2,1)="9",RIGHT(A2,8),RIGHT(A2,4))

or you need to convert the result of LEFT to a number again:

=IF(NUMBERVALUE(LEFT(A2,1))=9,RIGHT(A2,8),RIGHT(A2,4))

Upvotes: 2

ergonaut
ergonaut

Reputation: 7057

try =IF(INT(LEFT(A2,1))=9,RIGHT(A2,8),RIGHT(A2,4))

Upvotes: 0

Related Questions