Hi there, First post here so I apologize if I breach any rules. I have scoured the internet and this forum and so far been unsuccessful in finding a solution to my problem. Admittedly it is likely that I don't know the correct way to ask the question. Hopefully someone here can help me with this problem.
I have an excel csv file with approximately 3000 lines of text (it is a download from our website). I am trying to extract every mention of a colour within our product names to allow me to provide a more accurate filter capability. I have been trying to use the following as an array to solve my problem: =IFERROR(INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A2)),0)),"")
This has proven successful at extracting a single colour from my named range, however if a cell has multiple colour values this will only select the first in the list. My named ranges appear as follows (This isn't all my colours but just giving an example):
<tbody>
</tbody>
An example of my cell data would be:
Blue sweater with red stripes
black pants with orange pockets
green socks
[...]
I am hoping to compile all of discovered colours in a single cell (but can work with them all ending up individually in adjacent cells) to the right of the searched cell so the above would look as follows:
<tbody>
</tbody>
Any assistance anyone can provide would be greatly appreciated. As I have been pulling my hair out trying to get it to return multiple values.
Shaun
I have an excel csv file with approximately 3000 lines of text (it is a download from our website). I am trying to extract every mention of a colour within our product names to allow me to provide a more accurate filter capability. I have been trying to use the following as an array to solve my problem: =IFERROR(INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A2)),0)),"")
This has proven successful at extracting a single colour from my named range, however if a cell has multiple colour values this will only select the first in the list. My named ranges appear as follows (This isn't all my colours but just giving an example):
| things: | results: |
| red | red, |
| green | green, |
| blue | blue, |
| black | black, |
| orange | orange, |
<tbody>
</tbody>
An example of my cell data would be:
Blue sweater with red stripes
black pants with orange pockets
green socks
[...]
I am hoping to compile all of discovered colours in a single cell (but can work with them all ending up individually in adjacent cells) to the right of the searched cell so the above would look as follows:
| Blue sweater with red stripes | blue, red |
| black pants with orange pockets | black, orange |
| green socks | green |
<tbody>
</tbody>
Any assistance anyone can provide would be greatly appreciated. As I have been pulling my hair out trying to get it to return multiple values.
Shaun
