If cell contains text, replace it with a specific value

[Kindman_EG]

Dear Team,
I have an inquiry related to the above topic and appreciate to help and support.
I have applied the famous formula to the attached file as below:
=IF(O2="CERELAC", "CERELAC", "")
It has applied only on cells which contains only the specific text but the challenge here that there are a lot of cells contains the same word with another products but not reflected.
to summarize, i need an equation replacing the text with the new value even if it was mixed with many other words and many thanks in advance.
Regard,
Mohsen

 

[ETAF]

=IF(ISNUMBER(SEARCH(P$1,$O2,1)),"Cerelac","")

BUT as you have lots more columns - I suspect the out put "Cerelec" is not what you want in all columns and we could change to show the header

but again 1 cell W2 shows a different result

anyway the search() with isnumber() in an IF() will help

 

[Kindman_EG]

Thanks Etaf for your help, i still have the challenge that i need if the table result to be like below:

Products mentioned in the call	First product	Second product	Third product
CERELAC CERELACPUREE NIDO	CERELAC	CERELACPUREE	NIDO
S-26 GOLD 1 S-26-Goat	S-26 GOLD 1	S-26-Goat
S-26 GOLD 1 S-26 Ultima	S-26 GOLD 1	S-26 Ultima

I tried the text splitting using text to columns but it's not working properly in this sheet so i am looking for a formula to show the results as the above table (new simple file also is attached).
Please advise and many thanks again for your help and support.
Regards,
Mohsen

 

[ETAF]

thats a bit of a challenge - 1st row is OK , as the space is a delimiter , BUT not on the other 2 rows
and i suspect lots of different combination in the real data

i assume as you mention textsplit - you are using version 365 of excel

so i'm not sure how you would split that

All the samples are in your orginal sample sheet column O
NAN HMO NAN DUO = split NAN HMO / NAN DUO
S-26 GOLD KIDS S-26 LF GOLD = split S-26 GOLD KIDS / S-26 LF GOLD
PRENAN NAN1 OPTIPRO = ??? = PRENAN / NAN1 OPTIPRO or PRENAN NAN1 / OPTIPRO

so it short , sorry i do NOT know how to do this and get the split delimiter in the correct place
i will have a think - maybe some sort of table ......... might work

 

[Kindman_EG]

thats a bit of a challenge - 1st row is OK , as the space is a delimiter , BUT not on the other 2 rows
and i suspect lots of different combination in the real data

i assume as you mention textsplit - you are using version 365 of excel

so i'm not sure how you would split that

All the samples are in your orginal sample sheet column O
NAN HMO NAN DUO = split NAN HMO / NAN DUO
S-26 GOLD KIDS S-26 LF GOLD = split S-26 GOLD KIDS / S-26 LF GOLD
PRENAN NAN1 OPTIPRO = ??? = PRENAN / NAN1 OPTIPRO or PRENAN NAN1 / OPTIPRO

so it short , sorry i do NOT know how to do this and get the split delimiter in the correct place
i will have a think - maybe some sort of table ......... might work

Many thanks Etaf for your help, appreciate it

 

[ETAF]

you are welcome, hopefully another member may answer - i have reported to say i can not answer, in case a mod decides that removing my posts may provide more chance of a member replying based on the number of replies

 

[Luke M]

Formula you can put in P2, copied across and down.

=IF(ISNUMBER(SEARCH(P$1,$O2)),P$1,"")