Same Value from different number of values using MID & IF Functions

[Ajmal Khursand]

Hi MrExcel team,
I want to get USD value or AFN value from following different types of accounts Using IF, AND & OR functions however, it didn't work properly for some accounts result was OK but for all not hence kindly assist me which formula to use?
Note: in accounts starting with 1 if value number 7 is 1 its AFN account and if value number 7 is 2 its USD account, however, accounts starting with I & C if Value number 6 is 1 its AFN and if Value number 6 is 2 its USD.

ACC​	Currency​
10029111xxxxx​	AFN​
10029121xxxxx​	USD​
10029911xxxxx​	AFN​
10029921xxxxx​	AFN​
1002512xxxxxx​	USD​
1003511xxxxxx​	AFN​
I02911xxxxxxx​	AFN​
I02912xxxxxxx​	USD​
I02921xxxxxxx​	AFN​
I02991xxxxxxx​	AFN​
I14992xxxxxxx​	USD​
C02511xxxxxxx​	AFN​
C02512xxxxxxx​	USD​
C02591xxxxxxx​	AFN​
C09592xxxxxxx​	USD​
C02601xxxxxxx​	AFN​
C14611xxxxxxx​	AFN​
C02612xxxxxxx​	USD​

 

[etaf]

whats the Value Number ?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B
1	ACC	Currency
2	10029111xxxxx	AFN
3	10029121xxxxx	USD
4	10029911xxxxx	AFN
5	10029921xxxxx	USD
6	1002512xxxxxx	USD
7	1003511xxxxxx	AFN
8	I02911xxxxxxx	AFN
9	I02912xxxxxxx	USD
10	I02921xxxxxxx	AFN
11	I02991xxxxxxx	AFN
12	I14992xxxxxxx	USD
13	C02511xxxxxxx	AFN
14	C02512xxxxxxx	USD
15	C02591xxxxxxx	AFN
16	C09592xxxxxxx	USD
17	C02601xxxxxxx	AFN
18	C14611xxxxxxx	AFN
19	C02612xxxxxxx	USD
Main

Cell Formulas
Range	Formula
B2:B19	B2	=IF(LEFT(A2)="1",IF(MID(A2,7,1)="1","AFN","USD"),IF(OR(LEFT(A2)={"I","C"}),IF(MID(A2,6,1)="1","AFN","USD")))

 

[Ajmal Khursand]

=IF(LEFT(A2)="1",IF(MID(A2,7,1)="1","AFN","USD"),IF(OR(LEFT(A2)={"I","C"}),IF(MID(A2,6,1)="1","AFN","USD")))

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B
1	ACC	Currency
2	10029111xxxxx	AFN
3	10029121xxxxx	USD
4	10029911xxxxx	AFN
5	10029921xxxxx	USD
6	1002512xxxxxx	USD
7	1003511xxxxxx	AFN
8	I02911xxxxxxx	AFN
9	I02912xxxxxxx	USD
10	I02921xxxxxxx	AFN
11	I02991xxxxxxx	AFN
12	I14992xxxxxxx	USD
13	C02511xxxxxxx	AFN
14	C02512xxxxxxx	USD
15	C02591xxxxxxx	AFN
16	C09592xxxxxxx	USD
17	C02601xxxxxxx	AFN
18	C14611xxxxxxx	AFN
19	C02612xxxxxxx	USD
Main

Cell Formulas
Range	Formula
B2:B19	B2	=IF(LEFT(A2)="1",IF(MID(A2,7,1)="1","AFN","USD"),IF(OR(LEFT(A2)={"I","C"}),IF(MID(A2,6,1)="1","AFN","USD")))

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B
1	ACC	Currency
2	10029111xxxxx	AFN
3	10029121xxxxx	USD
4	10029911xxxxx	AFN
5	10029921xxxxx	USD
6	1002512xxxxxx	USD
7	1003511xxxxxx	AFN
8	I02911xxxxxxx	AFN
9	I02912xxxxxxx	USD
10	I02921xxxxxxx	AFN
11	I02991xxxxxxx	AFN
12	I14992xxxxxxx	USD
13	C02511xxxxxxx	AFN
14	C02512xxxxxxx	USD
15	C02591xxxxxxx	AFN
16	C09592xxxxxxx	USD
17	C02601xxxxxxx	AFN
18	C14611xxxxxxx	AFN
19	C02612xxxxxxx	USD
Main

Cell Formulas
Range	Formula
B2:B19	B2	=IF(LEFT(A2)="1",IF(MID(A2,7,1)="1","AFN","USD"),IF(OR(LEFT(A2)={"I","C"}),IF(MID(A2,6,1)="1","AFN","USD")))

Thank you so much it was really helpful and worked properly.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Peter_SSs]

If all of your values are one or the other of those currencies, as in your samples, then this should suffice

21 11 29.xlsm
	A	B
1	ACC	Currency
2	10029111xxxxx	AFN
3	10029121xxxxx	USD
4	10029911xxxxx	AFN
5	10029921xxxxx	USD
6	1002512xxxxxx	USD
7	1003511xxxxxx	AFN
8	I02911xxxxxxx	AFN
9	I02912xxxxxxx	USD
10	I02921xxxxxxx	AFN
11	I02991xxxxxxx	AFN
12	I14992xxxxxxx	USD
13	C02511xxxxxxx	AFN
14	C02512xxxxxxx	USD
15	C02591xxxxxxx	AFN
16	C09592xxxxxxx	USD
17	C02601xxxxxxx	AFN
18	C14611xxxxxxx	AFN
19	C02612xxxxxxx	USD
Sheet2

Cell Formulas
Range	Formula
B2:B19	B2	=IF(MID(A2,6+(LEFT(A2,1)="1"),1)="1","AFN","USD")