Search string and return multiple values

[ljphil78]

Hi everyone, my excel formula knowledge is pretty limited so was after a little help
I have a column where i want to check if the cell contains a certain letter and if it does it returns a value, if it contains another letter it will return a different value

for example

OP001
OP002
OP003
OB001
OB002
OG001
OG002
OG003

if the string contains "P" return "Purple"
if the string contains "B" return "Blue"
if the string contains "G" return "Green"

I tried using OR and IF but got very confused lol

 

[etaf]

how about
=IF(ISNUMBER(SEARCH("P",A2,1)),"Purple", IF(ISNUMBER(SEARCH("B",A2,1)),"Blue", IF(ISNUMBER(SEARCH("G",A2,1)),"Green", "")))

Book2
	A	B
1
2	OP001	Purple
3	OP002	Purple
4	OP003	Purple
5	OB001	Blue
6	OB002	Blue
7	OG001	Green
8	OG002	Green
9	OG003	Green
10
11	if the string contains "P" return "Purple"
12	if the string contains "B" return "Blue"
13	if the string contains "G" return "Green"
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=IF(ISNUMBER(SEARCH("P",A2,1)),"Purple", IF(ISNUMBER(SEARCH("B",A2,1)),"Blue", IF(ISNUMBER(SEARCH("G",A2,1)),"Green", "")))

 

[ljphil78]

Thank you for the quick reply, its amazing how quickly nesting gets messy lol

 

[DanteAmor]

Hi and welcome to MrExcel.

Try:

Dante Amor
	A	B
1
2	OP001	Purple
3	OP002	Purple
4	OP003	Purple
5	OB001	Blue
6	OB002	Blue
7	OG001	Green
8	OG002	Green
9	OG003	Green
Hoja5

Cell Formulas
Range	Formula
B2:B9	B2	=LOOKUP(2,1/(SEARCH({"P";"B";"G"},A2)),{"Purple";"Blue";"Green"})

 

[etaf]

yep,
I tend to use the false as a nest , rather than nest in true
but thats just me and how i prefer to see things laid out

you could use IFS() , but as that has to work out and evaulate all formulas for TRUE's i tend avoid that function