Extract & Convert String From Multiple Criteria

[muhammad susanto]

hi all..

how to extract data like this:

Book1
	D	E	F
1	criteria/contains text	data/text	Expected Result
2	BRI or Rakyat	PT. Bank Rakyat Indonesia (Persero) Tbk. Xxxxx	BRI
3	BRI or Rakyat	PT BRI (Persero), Tbk. Xxxxx	BRI
4	BRI or Rakyat	BRI xxxxxx	BRI
5	Mandiri	PT. Bank Mandiri (Persero) Tbk Regional xxxxxxxx	Mandiri
6	Mandiri	Bank Mandiri	Mandiri
7	BNI	PT. BNI (Persero) Tbk xxxxxx	BNI
8	Negara	PT. Bank Negara Indonesia (Persero), xxxxxxx	BNI
9	sssss	PT Bank TBN xxx	Other
10	rrrrrr	BSI …….	Other
Sheet1

criteria:
1. text contains string BRI or Rakyat ----so convert to be text " BRI"
2. text contains string mandiri----so convert to be text " Mandiri"
3. text contains string BNI or Negara ----so convert to be text " BNI"
4. text contains string other1. other2, other3, etc... ----so convert to be text " Other"
any help, would appreciated..
.sst

 

[muhammad susanto]

sorry maybe not clear
criteria:
1. text contains string BRI or Rakyat in Col.E2 down ----so convert/pull to be text " BRI" in Col F2 down
2. text contains string mandiri in Col. E2 down----so convert to be text " Mandiri" in Col F2 down
3. text contains string BNI or Negara in Col.E2 down----so convert to be text " BNI" in Col F2 down
4. text contains string not BRI or Mandiri or BNI etc..in Col.E2 down. ----so convert to be text " blank/empty" in Col F2 down

 

[muhammad susanto]

i think maybe more easy

Book1
	E	F
1	data/text	Expected Result
2	PT. Bank Rakyat Indonesia (Persero) Tbk. Xxxxx	BRI
3	PT BRI (Persero), Tbk. Xxxxx	BRI
4	BRI xxxxxx	BRI
Sheet1

contains text "BRI" or "Rakyat"

 

[muhammad susanto]

i think you can use partial match lookup , like this situation

Book1
	E	F	G
1	data/text
2	PT. Bank Rakyat Tbk. X
3	PT BRI (Persero), Tbk. Xxx
4
5	lookup1	lookup2	Expected Result
6	BRI	Rakyat	BRI
7	BRI	Rakyat	BRI
Sheet1

note:
data in col E2 down contains text "BRI' or "Rakyat" then maybe can lookup it with lookup1 & lookup2

my desired result in col G6 down..