Need help with characters

[susan hayes]

I need to identify account numbers that are 7 characters long where:

if the 7th digit ends in an A or E or S or T it is a Canadian dollar
account, and if it ends in B or F it is a U.S. Dollar account.

Eg. Acct# 123456A would equal CAD
Acct# 123456B would equal USD

How do I write an if statement where if the account # is found in cell
C3 and have the currency description identifier eg, "CAD" or "USD"
show up in cell D3

Your help would be appreciated

Thank you,
Susan

 

[Domenic]

=IF(--(ISNUMBER(MATCH(RIGHT(C3,1),{"A","E","S","T"},0)))>0,"CAD",IF(--(IS
NUMBER(MATCH(RIGHT(C3,1),{"B","F"},0)))>0,"USD",""))

Hope this helps!

 

[Ron Rosenfeld]

I need to identify account numbers that are 7 characters long where:

if the 7th digit ends in an A or E or S or T it is a Canadian dollar
account, and if it ends in B or F it is a U.S. Dollar account.

Eg. Acct# 123456A would equal CAD
Acct# 123456B would equal USD

How do I write an if statement where if the account # is found in cell
C3 and have the currency description identifier eg, "CAD" or "USD"
show up in cell D3

Your help would be appreciated

Thank you,
Susan

Possibly:

=IF(OR({"A","E","S","T"}=RIGHT(C3,1)),"CAD",IF(OR({"B","F"}=RIGHT(C3,1)),"USD","undefined"))


--ron

 

[Max]

Try in D3:

=VLOOKUP(RIGHT(TRIM(C3),1),{"A","CAD";"E","CAD";"S","CAD";"T","CAD";"B","USD
";"F","USD"},2,0)

Perhaps better with an error trap, put in D3:

=IF(ISNA(MATCH(RIGHT(TRIM(C3),1),{"A";"E";"S";"T";"B";"F"},0)),"",VLOOKUP(RI
GHT(TRIM(C3),1),{"A","CAD";"E","CAD";"S","CAD";"T","CAD";"B","USD";"F","USD"
},2,0))

 

[RagDyer]

You haven't mentioned whether or not there is the possibility of there being
*other* alphas besides the ones you mentioned.

If the *only* possibilities are what you stated, you could try this in D3:

=IF(OR(RIGHT(C3)={"B","F"}),"USD","CAD")

However, if there are other letters possible, try this:

=IF(OR(RIGHT(C3)={"B","F"}),"USD",IF(OR(RIGHT(C3)={"A","E","S","T"}),"CAD","
Unknown"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I need to identify account numbers that are 7 characters long where:

if the 7th digit ends in an A or E or S or T it is a Canadian dollar
account, and if it ends in B or F it is a U.S. Dollar account.

Eg. Acct# 123456A would equal CAD
Acct# 123456B would equal USD

How do I write an if statement where if the account # is found in cell
C3 and have the currency description identifier eg, "CAD" or "USD"
show up in cell D3

Your help would be appreciated

Thank you,
Susan

 

[Myrna Larson]

=IF(LEN(C3)<>7,"",IF(OR(RIGHT(C3)="B",RIGHT(C3)="F")),"USD","CAD"))