#### Fernandez30

##### New Member
Hi,

Please help me creating a formula, what i need is if a cell A2 contains a word "BRA" then it should give me the result as "Celma" so on and so forth.

BRA - CELMA, GBR- Caledonia, HUN - Hungary, USA - Lafayette, LHR - London, MYS- Malaysia, TWN - Taiwan, Anything else - Others

I created a formula however it is not working =IFERROR(IF(FIND("BRA",A2,1),"Celma",IF(FIND("GBR",A2,1),"Caledonia",IF(FIND("HUN",A2,1),"Hungary",IF(FIND("USA",A2,1),"Lafayette",IF(FIND("LHR",A2,1),"London",IF(FIND("MYS",A2,1),"Malaysia",IF(FIND("TWN",A2,1),"Taiwan"," "))))))),"Others")

#### Attachments

• 31 KB Views: 11

#### Attachments

• 32 KB Views: 10
• Anbuselvam K

#### Fernandez30

##### New Member
Its not necessary that it would start from 4.

#### vletm

##### Excel Ninja
Fernandez30
Where is 4?
If You would mean that those 'three letters' would in any position then there could be some challenges ... there could be eg text BRA anywhere!
In Your sample file, those 'three letters' are ALWAYS from 4th!

• Anbuselvam K

#### shrivallabha

##### Excel Ninja
With the sample data provided, you can use following formula in cell C2.
=IFERROR(MID(SUBSTITUTE(LOOKUP(2^15,FIND("-"&LEFT(\$D\$1:\$J\$1,3)&"-",A2&"-",1),\$D\$1:\$J\$1)," ",""),5,99),"Others")
Copy down the formula.

• Thomas Kuriakose

#### Fernandez30

##### New Member
IFERROR(MID(SUBSTITUTE(LOOKUP(2^15,FIND("-"&LEFT(\$D\$1:\$J\$1,3)&"-",A2&"-",1),\$D\$1:\$J\$1)," ",""),5,99),"Others")
Thanks you so much Shrivallabha, formula works as expected but i dont understand it all.

#### Fernandez30

##### New Member
With the sample data provided, you can use following formula in cell C2.
=IFERROR(MID(SUBSTITUTE(LOOKUP(2^15,FIND("-"&LEFT(\$D\$1:\$J\$1,3)&"-",A2&"-",1),\$D\$1:\$J\$1)," ",""),5,99),"Others")
Copy down the formula.
Would you mind explaining what is 2^15 :what does this mean

#### shrivallabha

##### Excel Ninja
Would you mind explaining what is 2^15 :what does this mean
"-"&LEFT(\$D\$1:\$J\$1,3)&"-" builds a string like -BRA-

Which is then used in FIND function to locate in source string through FIND("-"&LEFT(\$D\$1:\$J\$1,3)&"-",A2&"-",1)
Here the dash after A2 is added to handle if the string exists at end of source string.
This returns an array like {15,#VALUE,#VALUE,#VALUE...} i.e. where string is located it returns position of the string and #VALUE if it doesn't.

This ARRAY is then checked against maximum value of 2^15 (32768 which is one more than Excel cell specification limit 32767) to return the numerical result in above array (see the bold underlined value). You can use any large number instead of 2^15 e.g. 999 which should be higher than the number of maximum characters you may encounter.

This returns a result like BRA - Celma where we need to get rid of bold-underlined portion which we do by using one more SUBSTITUTE (as your data is not fully consistent) and MID functions.

Finally, we include cover specific cases i.e. \$D\$1:\$J\$1 so that the formula shall return error in other cases where we substitute with word others in all such cases using IFERROR.

You can see and test this all using Formula Evaluate option.

• Thomas Kuriakose

#### Fernandez30

##### New Member
Thank you so much for explaining it so beautifully. I did try the evaluate formula option was not able to understand 2^15, so thought of asking you.

Thanks a ton for all your time and effort on solving my problem.

Cheers!