Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

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

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: HOW TO: "If cell contains specific text then return specific text"

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Reuben Helder

Contributor

05-12-2017
03:14 PM
- last edited on
07-25-2018
09:36 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-12-2017
03:14 PM
- last edited on
07-25-2018
09:36 AM
by
TechCommunityAP

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?

Labels:

31 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-12-2017 10:12 PM

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)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-13-2017 12:04 PM

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

Spreadsheet Excellence,

Panaji, Goa, India

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-13-2017 12:16 PM

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

Spreadsheet Excellence,

Panaji, Goa, India

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-13-2017 01:09 PM

Vijaykumar Shetye, my formula does not need amending.

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-13-2017 10:37 PM

Dear Detlef Lewin,

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

Viaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-14-2017 12:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-14-2017 04:54 AM

Hi Reuben Helder,

Good day,

Please refer below formula to add more variable.

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-14-2017 05:10 AM

Hi Manoj,

It's worth to re-read task requirements and previous posts, your formula is about something different.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-14-2017 09:31 AM

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 **MECH**ANICAL ENGINEERS' or 'XYX **ARCH**ITECTS'. Hence the wild card character (*****) has been used.

Is it clear now?

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa India

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-14-2017 11:57 AM

SolutionI'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.

Best Response confirmed by
Reuben Helder (Contributor)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017 08:04 AM - edited 05-16-2017 08:21 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-16-2017 08:11 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-11-2019 12:48 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2019 02:00 AM

@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)`

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2019 05:53 AM

@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,

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2019 07:33 AM

@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)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2019 08:40 AM

@Sergei BaklanThanks it worked!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2019 08:47 AM

@scmallory , good, thank you for the confirmation

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-21-2019 12:01 PM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-27-2019 11:10 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-16-2019 03:40 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-16-2019 04:13 AM

```
=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})
```

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-28-2019 01:30 AM

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

Could you please help?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-30-2019 10:40 AM

@Detlef Lewin This works perfectly in most situations and is super simple, so I want to thank you for providing this! I haven't seen this solution offered anywhere else! The only problem I sometimes run into is that this isn't an exact match, so if I have a list of text that has some of the same terms or letter combinations, it sometimes returns the wrong thing. Is there a way to make this exact match only?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-30-2019 11:00 AM

Could you please attache the file instead of screenshort.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2020 03:06 AM

@Sergei Baklan I really like your solution with nested formatting, though I was wondering if there is a way to efficiently do a few hundred words?

There must be a better option than typing in all the words

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2020 07:48 AM

I gave nested IF only to illustrate that is workable. Formula which @Detlef Lewin suggested shall work perfectly, especially on big ranges.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-18-2020 11:02 PM

@Reuben Helder I am working on a spreadsheet that contains macros. In column A, users input an 'X' to make that row active which then gets copied for an email. If the row does not contain an 'X', the row is hidden and not copied for an email.

So what I am trying to accomplish:

IF a range of cells don't contain an 'X', THEN display "general text message".

If none of the cells from A2 through A10 contain an 'X', then I want a message to be displayed in B1. However, if any of the cells from A2 though A10 contain an 'X', then B2 should not display a message.

If there is a way to accomplish this, is it possible to have the formula automatically adjust to a new range of cells, ie. if I were to add a row between A2 and A10.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-15-2020 11:39 PM

I have a problemm where I need to filter out the state and district name from the address column and place the same in the adjoining column

State llist can be arranged as a range. But what would be the formula to use and how will it be used to math the address column

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-25-2020 03:06 PM

Hi was googling for a solution to the subject of above thread and found and used your solution,

[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) **]with 1 problem; the keyword found doesn't display.

using Excel 2010

would appreciate any help

thanks!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-27-2020 08:18 AM

by krisi042 on October 28, 2019

by Deleted on August 01, 2019

by Blue-Moon on June 20, 2019

by Alejandro Murillo Ramirez on September 27, 2018

Microsoft Store

Education

Developer