Count if cell contains text string

stuartw

Board Regular
Joined
Dec 5, 2007
Messages
237
Hi guys

Probably an easy one for you lot but here goes.

I have 1000 rows of text containing different things (text). I want to count the cell if it contains a text string.

eg Cell contains "Microsoft Navision". I want to search to see if the cell contains "nav" (just the string as opposed to the whole product)

Can anyone help?

Thanks in advance
Stu
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Thanks - that gives me what I asked for - can't ask for more than that. I think I phrased the question wrong though :P

Using similar logic, how can I incorperate that into an IF statement? i.e I want the line to say "TRUE" if that line contains my criteria and then drag the formula down.

Thanks!
 
Upvote 0
You can modify it to:

=IF(COUNTIF(A1:C1,"*" & "nav" & "*"),"True","False")

and change the range to suit.
 
Upvote 0
You can modify it to:

=IF(COUNTIF(A1:C1,"*" & "nav" & "*"),"True","False")

and change the range to suit.
How should I modify above string if I'd like to know how many times the phrase "xyz." is found from the "Sheet1!$A:$A"?

Phrase "xyz" can be anywhere in the word so "=COUNTIF(Sheet1!$a:$a;xyz)" won't work.
 
Last edited:
Upvote 0
I think...

=IF(COUNTIF(Sheet1!$A:$A,"*" & "xyz" & "*"),"True","False")

I thought the "*" bits would denote a wildcard search?
 
Last edited:
Upvote 0
You can modify it to:

=IF(COUNTIF(A1:C1,"*" & "nav" & "*"),"True","False")

and change the range to suit.

How could you do this between dates? And would it be possible to sum another range based on the text string (between dates as well)?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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