Look for specific text within other text, separated by commas

[geigMR]

Hello

I need to know a formula to look for specific text within other text, and if found display that text. The text items in the cell are always separated by commas, but there other irrelevant text items in the same cell.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Example:<o></o>
column B to look to see whether column A contains “red”, or “Bill and Ben”, or “big” and display all of these words found.<o></o>
A1: red,green,blue,black,Bill and Ben,Flower Pot Men,big,small<o></o>
B1: red,Bill and Ben,big<o></o>
<o> </o>
A2: red,white,green,blue,orange,black,Flower Pot Men,big,small<o></o>
B2: red,small<o></o>
<o> </o>
A3: white,green,blue,purple,black,Flower Pot Men,big,medium,short<o></o>
B3: big<o></o>
<o> </o>
It is important that column B maintains the separation of words by commas.<o></o>
<o> </o>
If it’s not possible to pull out the words I need, perhaps it’s possible to exclude/delete the other unnecessary words?<o></o>
<o> </o>
Many thanks for any help! <o></o>

 

[number kruncher]

Why does Cat Table have comma's on either end of the key words?

Surely this must discount them from the search, if they are the first or last entry in your main string.

 

[geigMR]

aarrrgh...thought i was almost there...
(I had just worked out how to remove the double commas between inner strings, as well as commas at the beginning and end of the entire string too!)

You're right.

The reason I put in those commas is because otherwise you get duplication of words as inner strings when they 'shouldn't' be there, e.g.

A4: mango,kiwi,banana,apple,pink and red,brown,triangular and round,oval
becomes
B4: pink and red,pink,red,brown,
instead of
B4: pink and red,brown,

Just because "red" is an 'inner string' itself as shown in Cat Table, it should not appear in B4, unless it is included in A4 in its own right. I need to only show the 'inner strings' exactly as they are featured in column A.
Another example: a "coat hook" is different from a "coat" but with the current formula both would appear for a coat hook.

Hmmmm....head is feeling v scratched

 

[number kruncher]

You could use SUBSTITUTE to change your initial data from the off, by using..

=SUBSTITUTE(A2," ","-")

In this example, you could then change all the "spaces" in Cat Table to "-".

Then your exact string count would work.

HTH

P.S. This is one of the most bizarre text searches ever !!

 

[geigMR]

thanks for bearing with it!! It must seem bizarre but this is honestly going to make a massive difference once I nail it...which I think with your amazing help I've pretty much done...

Thanks for your previous suggestion. I tested that but it doesn't seem to make any difference:

Excel Workbook
	A	B	C	D	E
1		FRUIT	COLOUR	SHAPE	COUNTRY
2	mango,kiwi,banana,apple,pink-and-red,brown,triangular-and-round,oval	mango,,,,,,,	pink-and-red,pink,red,,,,	triangular-and-round,,,,,,,	,,,,,,
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A2,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$7&"*"),'cat table'!A$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$8&"*"),'cat table'!A$8&",","")&IF(COUNTIF($A2,"*"&'cat table'!#REF!&"*"),'cat table'!#REF!&",","")
C2	=IF(COUNTIF($A2,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$7&"*"),'cat table'!B$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$8&"*"),'cat table'!B$8&",","")
D2	=IF(COUNTIF($A2,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$7&"*"),'cat table'!C$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$8&"*"),'cat table'!C$8&",","")
E2	=IF(COUNTIF($A2,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$7&"*"),'cat table'!D$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$8&"*"),'cat table'!D$8&",","")

Excel Workbook
	A	B	C	D
1	FRUIT	COLOUR	SHAPE	COUNTRY
2	mango	pink-and-red	triangular-and-round	spain
3		pink
4		blue-and-red
5		red
cat table

#VALUE!

Excel 2003

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A2,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$7&"*"),'cat table'!A$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!A$8&"*"),'cat table'!A$8&",","")&IF(COUNTIF($A2,"*"&'cat table'!#REF!&"*"),'cat table'!#REF!&",","")
B3	=IF(COUNTIF($A3,"*"&'cat table'!A$2&"*"),'cat table'!A$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$3&"*"),'cat table'!A$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$4&"*"),'cat table'!A$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$5&"*"),'cat table'!A$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$6&"*"),'cat table'!A$6&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$7&"*"),'cat table'!A$7&",","")&IF(COUNTIF($A3,"*"&'cat table'!A$8&"*"),'cat table'!A$8&",","")&IF(COUNTIF($A3,"*"&'cat table'!#REF!&"*"),'cat table'!#REF!&",","")
C2	=IF(COUNTIF($A2,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$7&"*"),'cat table'!B$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!B$8&"*"),'cat table'!B$8&",","")
C3	=IF(COUNTIF($A3,"*"&'cat table'!B$2&"*"),'cat table'!B$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$3&"*"),'cat table'!B$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$4&"*"),'cat table'!B$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$5&"*"),'cat table'!B$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$6&"*"),'cat table'!B$6&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$7&"*"),'cat table'!B$7&",","")&IF(COUNTIF($A3,"*"&'cat table'!B$8&"*"),'cat table'!B$8&",","")
D2	=IF(COUNTIF($A2,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$7&"*"),'cat table'!C$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!C$8&"*"),'cat table'!C$8&",","")
D3	=IF(COUNTIF($A3,"*"&'cat table'!C$2&"*"),'cat table'!C$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$3&"*"),'cat table'!C$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$4&"*"),'cat table'!C$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$5&"*"),'cat table'!C$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$6&"*"),'cat table'!C$6&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$7&"*"),'cat table'!C$7&",","")&IF(COUNTIF($A3,"*"&'cat table'!C$8&"*"),'cat table'!C$8&",","")
E2	=IF(COUNTIF($A2,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$7&"*"),'cat table'!D$7&",","")&IF(COUNTIF($A2,"*"&'cat table'!D$8&"*"),'cat table'!D$8&",","")
E3	=IF(COUNTIF($A3,"*"&'cat table'!D$2&"*"),'cat table'!D$2&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$3&"*"),'cat table'!D$3&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$4&"*"),'cat table'!D$4&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$5&"*"),'cat table'!D$5&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$6&"*"),'cat table'!D$6&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$7&"*"),'cat table'!D$7&",","")&IF(COUNTIF($A3,"*"&'cat table'!D$8&"*"),'cat table'!D$8&",","")

with:

Excel Workbook
	A	B	C	D
1	FRUIT	COLOUR	SHAPE	COUNTRY
2	mango	pink and red	triangular and round	spain
3		pink/
4		blue and red
5		red/
cat table

Excel 2003



I can then 'paste special' the results as values, then apply these two formulas to get the end results I require:
C32: =SUBSTITUTE(D32,"/","") removes the "/"
B32: =IF(RIGHT(C32,2)=",,",LEFT(C32, FIND(",,",C32)-1),LEFT(C32,LEN(C32)-1)) removes the trailing comma or commas.
So from "pink and red,pink/,red/,,,,"
I get "pink and red,pink,red"
I've tried for ages to work out how I can combine these two formulas in one cell (C32) - is there any way??

So I think I'm really close to the solution to it all. The one thing that worries me is if there is going to be a limit to the number of COUNTIF statements I can have in each cell. I've tested up to 10 but I'm going to need a lot more. Which is why I was hoping to find a way of selecting the range e.g. B2:B50 in Cat Table rather than typing out 50 statements...

Thanks again so much for helping me, my life has been on hold this week..!

 

[number kruncher]

I am pleased you are nearly there, it is very satisfing to solve such a problem.

AFAIK there is no limit to the number of COUNTIFS in one cell, however, you will be limited to the number of characters in each cell, which for 2007 stands at..

=(8^5)-1 = 32,768 chars per cell

If you break this, please let me know

HTH

 

[zopfan]

Welcome to the forum

You could use a COUNTIF with wildcards eg.

=IF(COUNTIF(A1,"*"&"red"&"*"),"red"&",","")&IF(COUNTIF(A1,"*"&"Bill and Ben"&"*"),"Bill and Ben"&",","")&IF(COUNTIF(A1,"*"&"big"&"*"),"big",""

HTH

Sir, I needed this function very much. But this is a bit too much for me you know. Can you pls elaborate it a bit. I would be highly obliged.

P.S. I know this thread is rather old. But still I expect you to reply considering the effort you took to compile the same.