Search for Text in column

[mikeymay]

Without VBA, is it possible to return the number of times a text string appears in a column?

Basically I need to count how many times the strings in the Test List column appear in the Search String column


TIA

 

[Peter_SSs]

You may need to clarify exactly what you mean by this.

how many times the strings in the Test List column appear in the Search String column

For example.

1. If the Search String column contained "Text 15 | Text 2" then the string "Text 1" is found within that. Would you want that counted?
   
   
2. If the Search String column contained "Text 1 | Text 2 | Text 1" would you want "Text 1" counted once or twice for that row?

 

[mikeymay]

You may need to clarify exactly what you mean by this.


For example.

1. If the Search String column contained "Text 15 | Text 2" then the string "Text 1" is found within that. Would you want that counted?
   
   
2. If the Search String column contained "Text 1 | Text 2 | Text 1" would you want "Text 1" counted once or twice for that row

 

[mikeymay]

A fair point...I think I need to include a suffix of ' | ' at the end so that each text string is unique

So the Search Strings would be
Text 1 | Text 5 |
Text 2 | Text 1 | Text 4 |

 

[mikeymay]

You may need to clarify exactly what you mean by this.


For example.

1. If the Search String column contained "Text 15 | Text 2" then the string "Text 1" is found within that. Would you want that counted?
   
   
2. If the Search String column contained "Text 1 | Text 2 | Text 1" would you want "Text 1" counted once or twice for that row?

Each of the possible items in the text List will only every appear once. There would never be an instance of Text 1 | Text 2 | Text 1 |

 

[Peter_SSs]

There would never be an instance of Text 1 | Text 2 | Text 1 |

OK, thanks.

..I think I need to include a suffix of ' | ' at the end so that each text string is unique

Put one at the start as well then try something like this

24 08 05.xlsm
	A	B	C	D
1
2	Text 1	2		|Text 1|Text 5|
3	Text 2	1		|Text 2|Text 1|Text 4|
4	Text 3	0
5	Text 4	1
6	Text 5	1
Count String

Cell Formulas
Range	Formula
B2:B6	B2	=BYROW(A2:A6,LAMBDA(r,COUNTIF(D2:D3,"*|"&r&"|*")))
Dynamic array formulas.

 

[Phuoc]

Try this:

Book1
	A	B	C	D
1
2	Text 1	2		Text 1 | Text 5 |
3	Text 2	1		Text 2 | Text 1 | Text 4 |
4	Text 3	0
5	Text 4	1
6	Text 5	1
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=COUNTIF($D$2:$D$3,"*"&A2&" |*")

 

[mikeymay]

OK, thanks.


Put one at the start as well then try something like this

24 08 05.xlsm
	A	B	C	D
1
2	Text 1	2		|Text 1|Text 5|
3	Text 2	1		|Text 2|Text 1|Text 4|
4	Text 3	0
5	Text 4	1
6	Text 5	1
Count String

Cell Formulas
Range	Formula
B2:B6	B2	=BYROW(A2:A6,LAMBDA(r,COUNTIF(D2:D3,"*|"&r&"|*")))
Dynamic array formulas.

Great stuff!!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.