Substitute comma-separated text in one cell with values from an array

[epmd]

Hello all,

Here is what I am trying to do:

I want to convert the comma-separated names in column A to their common ingredient names with the output in Column B, still comma-separated. I would have a chart in another tab (screenshot below) that lists each latin name in column A and its common name in column B. However, if the one of the values in the string in column A isn't found in the chart below (for example, Parfum), it would just return that value in the string in column B. How would this be accomplished? Please note that I would have about 200 rows in the original file to be converted from Latin to common name.

 

[RoryA]

It would be much easier for people to try and help if you posted the data using XL2BB rather than as a picture. Also, if it needs to work on 2016 as well as 365, it will be more complicated, unless Power Query is an option?

 

[epmd]

It would be much easier for people to try and help if you posted the data using XL2BB rather than as a picture. Also, if it needs to work on 2016 as well as 365, it will be more complicated, unless Power Query is an option?

Sorry, thanks, I will get XL2BB installed and repost. Power Query might be an option if it will make the process simpler. I'm using Excel 365, so I'm fine with it just working on this version.

 

[epmd]

Here is what I am trying to do:

INCITerms.xlsx
	A	B
1	Latin ingredient name	Common Name
2	Ricinus Communis Seed Oil, Parfum, Butyrospermum Parkii Butter, Glycine Soja Oil, Simmondsia Chinensis (Jojoba) Seed Oil, Tocopherol	Castor Oil, Parfum, Shea Butter, Soybean Oil, Jojoba Oil, Vitamin E
Sheet1

I want to convert the comma-separated names in column A to their common ingredient names with the output in Column B, still comma-separated. I would have a chart in another tab (screenshot below) that lists each latin name in column A and its common name in column B. However, if the one of the values in the string in column A isn't found in the chart below (for example, Parfum), it would just return that value in the string in column B. How would this be accomplished? Please note that I would have about 200 rows in the original file to be converted from Latin to common name.

INCITerms.xlsx
	A	B
1	INCI Term	Common Name
208	Macadamia Ternifolia (Macadamia Nut) Seed Oil	Macadamia Nut Oil
218	Melaleuca Alternifolia (Tea Tree) Leaf Oil	Tea Tree Essential Oil
219	Melaleuca Leucadendron Cajaputi (Cajeput) Leaf Oil	Cajeput Essential Oil
220	Melia Azadirachta (Neem) Seed Oil	Neem Oil
221	Melissa Officinalis (Balm Mint) Leaf Oil	Melissa Essential Oil
223	Mentha Arvensis (Cornmint) Oil	Cornmint Essential Oil
224	Mentha Piperia (Peppermint) Oil	Peppermint Essential Oil - Natural
225	Mentha Piperia (Peppermint) Oil	Peppermint Essential Oil - Redistilled
228	Mentha Spicata (Spearmint) Leaf Oil	Spearmint Essential Oil
231	Monarda Fistulosa Menthaefolia Oil	Monarda Essential Oil
Table 1

 

[Fluff]

How about

=LET(ary,FILTERXML("<k><m>"&SUBSTITUTE(A2,", ","</m><m>")&"</m></k>","//m"),TEXTJOIN(", ",,IFNA(XLOOKUP(ary,'Table 1'!$A$2:$A$232,'Table 1'!$B$2:$B$232,,0),ary)))

 

[epmd]

How about

=LET(ary,FILTERXML("<k><m>"&SUBSTITUTE(A2,", ","</m><m>")&"</m></k>","//m"),TEXTJOIN(", ",,IFNA(XLOOKUP(ary,'Table 1'!$A$2:$A$232,'Table 1'!$B$2:$B$232,,0),ary)))

Awesome! Thank you, that worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.