Excel formula: "IF" a cell contains a word, then? Solved

Question

Hello everyone,  I've been struggling for several days and haven't found any answers on the forums.  Here's the problem: In column A, I have text (for example, LAST NAME FIRST NAME) I would like to put a formula in column B that gives me a result based on part of the text in A. Example: if A1 contains "Fabien" or "Franck" or other names (about 10 possibilities), then it returns "student", otherwise, nothing.  Am I clear enough for someone to help me?  Thanks in advance for any tips      Configuration: Windows / Firefox 54.0

Mike-31 · Accepted Answer

Good evening,  One formula among others  =IF(OR(COUNTIF(A2,"*franck*"),COUNTIF(A2,"*michel*"),COUNTIF(A2,"*fabien*")),"student","")   It's up to you to add COUNTIF(A2,"*franck*") without forgetting the asterisks Cheers Mike-31  I am responsible for what I say, not for what you understand...

DjiDji59430 · Answer

Hello,  What is the exact format of your text? Is it valid for the entire name/first name column? And can't you separate the name and first name into two columns??  Hello,  Please attach a test file (with ALL the explanations and desired results) or better yet, if possible, the relevant file; it’s more convenient for the participants.  1) Go to https://www.cjoint.com/ 2) Click on [Browse] to select your file 3) Scroll down to the bottom of the page to click on [Create Cjoint link] 4) After a few seconds, the link will appear in blue underline; select it and click "Copy" 5) Return to the discussion, and in your reply message, click "Paste."  Best regards.

RemiDC · Answer

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($F$2:$F$12,B2)))>0,A2) With $F$2:$F$12 => List of words you are looking for (each in a cell) With B2 > the cell where you are searching With A2 > what you return if true If cell B2 contains one of the words from cells $F$2:$F$12, then it displays A2 Broken down: * SEARCH(lookup text, text in which you search) returns the index of the character from which the searched word is found * ISNUMBER returns TRUE if the result of SEARCH is a number (thus if one of the words is found) * The problem with SEARCH is that it displays the result for each word in different cells: meaning that if you search for 5 words it will "expand" over 5 cells below your formula >> The SUMPRODUCT function serves to concatenate these results to have them in 1 cell * The IF function allows you to have a value for TRUE and/or FALSE instead of the words TRUE / FALSE Source: https://fr.extendoffice.com/documents/excel/3796-excel-check-if-cell-contains-one-of-several-values.html#a1

valy · Answer

Hello, Can you help me with this formula please? Here is the problem:  I have text in column A (name FIRSTNAME)  I would like to put a formula in column B that gives me a result based on part of the text from A.  Example: if A1 contains "Fabien" or "Franck" or other names (about 10 possibilities), then it returns "adv", otherwise nothing; or if A1 contains "Aline" or "Véro" or other names (about 10 possibilities), then it returns "tech", otherwise nothing.  Depending on the names, I would then have adv or tech in column B Thank you in advance

Mike-31 · Answer

Hello, You can write the formula like this: the first formula for handling Male First Names =IF(SUM(COUNTIF(A1,"Fabien"),COUNTIF(A1,"Frank"),COUNTIF(A1,"Pierre"),COUNTIF(A1,"Marc"))>0,"adv","") the formula for Female First Names IF(SUM(COUNTIF(A1,"Aline"),COUNTIF(A1,"Véro"),COUNTIF(A1,"Marie"),COUNTIF(A1,"Ginette"))=1,"tech","") and you combine the two formulas with & between the two formulas =IF(SUM(COUNTIF(A1,"Fabien"),COUNTIF(A1,"Frank"),COUNTIF(A1,"Pierre"),COUNTIF(A1,"Marc"))>0,"adv","")&IF(SUM(COUNTIF(A1,"Aline"),COUNTIF(A1,"Véro"),COUNTIF(A1,"Marie"),COUNTIF(A1,"Ginette"))=1,"tech","")  See you+ Mike-31  I am responsible for what I say, not for what you understand…

Formule Excel : "SI" une cellule contient un mot, alors ?

5 answers

Jbl 500 bt one-sided issue.

Remove youtube from freebox pop apps

Subliminal image in a video

I'm looking for java or jar game sites.

How to unsubscribe from jm plan cul

Edit ad on leboncoin for free

Commun.file_upload.maxsize

Problem opening modal window from another page

Thursday modification test title number 3

How to change sources on a grundig universal remote?