Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

onlyforsubu

New Member
Joined
May 15, 2016
Messages
1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Purple! Pink! Gold! Nude! Orange! Yellow! Brown[/TD]
[/TR]
[TR]
[TD]Ivory! Black! Green! Silver! Purple! Pink! Nude[/TD]
[/TR]
[TR]
[TD]Purple! Pink! Gold! Nude! Orange! Yellow! Brown[/TD]
[/TR]
</tbody>[/TABLE]

Above are values & we have another sheet of categories. In categories there are numbers like below

[TABLE="width: 330"]
<tbody>[TR]
[TD="align: center"]Red[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]White[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]Ivory[/TD]
[TD="align: center"]82[/TD]
[/TR]
[TR]
[TD="align: center"]Blue[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]Black[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]Green[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="align: center"]Silver[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]Purple[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]Pink[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]Gold[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Nude[/TD]
[TD="align: center"]81[/TD]
[/TR]
[TR]
[TD="align: center"]Orange[/TD]
[TD="align: center"]83[/TD]
[/TR]
[TR]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]90[/TD]
[/TR]
[TR]
[TD="align: center"]Brown[/TD]
[TD="align: center"]91
[/TD]
[/TR]
</tbody>[/TABLE]

We want above colors in numbers like

17,22,25........ etc. in same cell

Can someone help me please ????
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi,

Welcome to MrExcel Forum!

To be honest, the idea of storing multiple pieces of information in a single cell is unfortunate. Sooner or later it causes problems, which you are now experiencing: your task is quite easy when the color names are located in spearate cells, and difficult otherwise. Many other problmes may surface later.

What is the reason for keeping all the names together?


J.Ty.
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top