• 462K Members
• 6,195 Online
• 559K Conversations
SOLVED

Contributor

# HOW TO: "If cell contains specific text then return specific text"

I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell.

This formula seems to work for two variables but I can't add any more variables too it.

=IFERROR(IF(SEARCH("*Sales*",B3,1),"Sales"),IF(SEARCH("*Arch*",B3,1),"Architecture"))

The text I would be searching for would be:

Sales,

Arch,

Land,

ALL,

Contracts,

Construction

and possibly a couple more.

Is there a way to do this?

23 Replies

# Re: HOW TO: "If cell contains specific text then return specific text"

Ah, a classic one.

Key words in column F, returned text in column G, text to check in A1.

`=LOOKUP(PI(),1/COUNTIF(A1,"*"&\$F\$1:\$F\$6&"*"),\$G\$1:\$G\$6)`

# Re: HOW TO: "If cell contains specific text then return specific text"

Dear Reuben Helder,

You may also use the below formula for the task. I have retained the same cell references that have been used by Detlef Lewin in the solution that he has provided.
=INDEX(\$F\$1:\$F\$6,SUM(COUNTIF(\$A1,"*"&\$F\$1:\$F\$6&"*")*ROW(F1:F6)))

The formula given by Detlef Lewin can be amended, so that Pi() and column G will not be required.
=LOOKUP(1,1/COUNTIF(\$A1,"*"&\$F\$1:\$F\$6&"*"),\$F\$1:\$F\$6)

Vijaykumar Shetye
Panaji, Goa, India

# Re: HOW TO: "If cell contains specific text then return specific text"

Dear Reuben Helder,

Kindly note that the below formula which I had given in the provious post, is an array formula.
=INDEX(\$F\$1:\$F\$6,SUM(COUNTIF(\$A1,"*"&\$F\$1:\$F\$6&"*")*ROW(\$F\$1:\$F\$6)))

Array formulas are entered using Control+Shift+Enter instead of Enter.

Vijaykumar Shetye
Panaji, Goa, India

# Re: HOW TO: "If cell contains specific text then return specific text"

Vijaykumar Shetye, my formula does not need amending.

And your change from PI() to 1 will possibly give wrong results.

# Re: HOW TO: "If cell contains specific text then return specific text"

Dear Detlef Lewin,

Thanks for the reply and the correction. What is the significance of using Pi() in the formula?

Viaykumar Shetye,

Panaji, Goa, India

# Re: HOW TO: "If cell contains specific text then return specific text"

PI() itself is just a little gag. The lookup value has to be a number greater than 1. So 1.1 would be enough or 2 if you prefer whole numbers.

Highlighted

# Re: HOW TO: "If cell contains specific text then return specific text"

Hi Reuben Helder,

Good day,

If statment is false then I have considered criteria is "NO MATCH"

IF(B3="MECH","MECHANICAL",IF(B3="ARCH","ARCHITECTURE",IF(B3="SALES","SALES",IF(B3="ELE","ELECTRICAL","NO MATCH"))))

Is this helful for you? please revert bach.

Thanks & regards,

Manoj P.

Hi Manoj,

# Re: HOW TO: "If cell contains specific text then return specific text"

Dear Manoj Patgar,

(1)  The problem has been posted on the forum by Reuben Helder because the formula using nested If functions is not adequate, due to its limitations.

The number of items in the text list is large (Sales, Arch, Land, ALL, Contracts, Construction and possibly a couple more), which nested IFs cannot handle. So the same formula with minor changes cannot work. The formula to be used has to be disigned to handle a long list of data.

(2)  The data which is being searched is not MECH or ARCH. It will be like Searching MECH or ARCH within a data list that contain entries like 'ABC MECHANICAL ENGINEERS' or 'XYX ARCHITECTS'. Hence the wild card character (*) has been used.

Is it clear now?

Vijaykumar Shetye,

Panaji, Goa India

Solution

# Re: HOW TO: "If cell contains specific text then return specific text"

I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.

But what to use depends on goals, in some cases quick hardcording works quite fine.

If use formatting nested if becomes much more clear and editable. Like this

`=IF(ISNUMBER(SEARCH("Sales",        B3,1)),"Sales", IF(ISNUMBER(SEARCH("Arch",         B3,1)),"Architecture", IF(ISNUMBER(SEARCH("Land",         B3,1)),"Land", IF(ISNUMBER(SEARCH("All",          B3,1)),"All", IF(ISNUMBER(SEARCH("Contracts",    B3,1)),"Contracts", IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction", "No Match" )))))) `

If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.

``` =IF(ISNUMBER(SEARCH(\$F\$1,B3,1)),\$G\$1,
IF(ISNUMBER(SEARCH(\$F\$2,B3,1)),\$G\$2,
IF(ISNUMBER(SEARCH(\$F\$3,B3,1)),\$G\$3,
IF(ISNUMBER(SEARCH(\$F\$4,B3,1)),\$G\$4,
IF(ISNUMBER(SEARCH(\$F\$5,B3,1)),\$G\$5,
IF(ISNUMBER(SEARCH(\$F\$6,B3,1)),\$G\$6,
IF(ISNUMBER(SEARCH(\$F\$7,B3,1)),\$G\$7,
IF(ISNUMBER(SEARCH(\$F\$8,B3,1)),\$G\$8,
IF(ISNUMBER(SEARCH(\$F\$9,B3,1)),\$G\$9,
"No Match"
)))))))))
```

I don't vote for nested IF,  i would like to say where is no limitations here. What to use that's concrete person choice.

# Re: HOW TO: "If cell contains specific text then return specific text"

Dear Mr. Sergi,

Yes. You are absolutely right.

<it is worth to re-read the question>

I am a beginner in excel and I am always welcome one who gives valuable suggestion.

With best regards,

Manoj.

# Re: HOW TO: "If cell contains specific text then return specific text"

Dear Mr. Vijaykumar,

Thank you and appreciate your support.

I am beginner and I am always welcome one who want to give valuable suggestions.

Thanks with best regards,

Manoj.

# Using =IF formula to calculate spending on groceries

Hi I'm looking to create a formula to calculate how much I spend on groceries a year. For an example:

=IF(B1="nofrills","Loblaws","sobeys"

then display C1 (the \$\$)

otherwise false

# Re: Using =IF formula to calculate spending on groceries

@scmallory , that's a separate question, better to start new conversation with it from here.

Formula could be

`=IF(SUM(--(B1={"nofrills","Loblaws","sobeys"})),C1)`

# Re: Using =IF formula to calculate spending on groceries

@Sergei BaklanI'm having troubles posting in a new thread.

I tested out the formula and I was hoping cells in column D it would spit out the value in column C for anywhere it says "Food Basic" in column B. So the attached Test document.

Thank you,

# Re: Using =IF formula to calculate spending on groceries

@scmallory , to start new conversation go to the General Discussion as in my link and click

Formula doesn't work since in column B you have "food basics ", and you compare with "food basics". Due to space texts are different. Trim the cell like

`=IF(SUM(--(TRIM(B1)={"nofrills","food basics","Loblaws","sobeys"})),C1)`

# Re: Using =IF formula to calculate spending on groceries

@Sergei BaklanThanks it worked!

# Re: Using =IF formula to calculate spending on groceries

@scmallory , good, thank you for the confirmation

# Re: HOW TO: "If cell contains specific text then return specific text"

Not OP, but this was exactly what I was looking for, thank you!

# Re: HOW TO: "If cell contains specific text then return specific text"

I am looking for a formula to do the following. I believe it is an "IF", but not sure. I basically want a formula to return results based on the following:

If the cell with the number is <=50, then multiply by 2

If the cell with the number is >50, but <=100, then multiply by 1.75

If the cell with the number is >100, but <=200, then multiply by 1.50

I am trying to combine in one formula so that I can copy it all the way down the spreadsheet. Any help would be greatly appreciated.

# Re: HOW TO: "If cell contains specific text then return specific text"

@Detlef Lewin  Thank you so much - I spent so long looking for this - it works perfectly!

# Re: HOW TO: "If cell contains specific text then return specific text"

``````=A1*IF(A1>200,1,IF(A1>100,1.5,IF(A1>50,1.75,2)))
or
=A1*LOOKUP(A1,{0,50.000001,100.000001,200.000001},{2,1.75,1.5,1})``````

# Re: Using =IF formula to calculate spending on groceries

@Sergei Baklan this is exactly what I`m looking for but excel gives me an error - attached.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies