View original (French)

[Excel] COUNTIF with partial text string

Solved
lecastorlapon -  
 LGLCNfamily -
Hello,

I am trying to count the number of cells that contain a certain text that is only a part of the value of the cell. Example:
Cell 1: Marc is handsome
Cell 2: Marc is rich
Cell 3: Sophie is beautiful

I want a formula that counts the number of cells where "Marc" appears, which is 2 here. I think I need to tweak the COUNTIF function, but I don't know how.

Thank you for your help

Configuration: Windows 7 / Firefox 8.0

1 réponse

Réponse 1 / 1
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Good evening 
=COUNTIF(A1:A100,"*Marc*")

or 
=COUNTIF(A1:A100,"*"&B1&"*")

by placing the searched text in B1
Regards
111
lecastorlapon
 
It seems to be working, great! I didn't know about this thing with asterisks. Does it work in all Excel formulas?
Anyway, thanks for the tip.
0
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453 > lecastorlapon
 
Hello lacastorlapon(!!?)

No, this does not apply, for example, with formulas that use a mathematical operator such as +, - > < as a field separator

but only via the semicolon
for example:
=IF(A1="*A*"... does not work

however:
VLOOKUP("*A*";Field... works. as well as MATCH("*"&B1&"*";Field)
the same with SUMIF("*"&B1&"*";.....
Etc...

additionally and for your information
if you write
"*"&B1 or "*text", the formula only searches for the first word of the text
with
B1&"*" or "text*", with the last word surrounded by two asterisks, the code searches anywhere in the text.

Safe travels

Best regards
4
lecastorlapon > lecastorlapon
 
Cool. In fact, it's like a meta character, I use it when I do Windows searches.

Otherwise, for mono pseudo, it comes from my nickname (Castor) and a sketch by Les Nuls about Lapp beavers.

See you!
0
LGLCNfamily
 
I searched for hours before finding your post. Thank you!!!
0