 SOLVED

Highlighted
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?

31 Replies
Highlighted

# 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.

Highlighted

# 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!

Highlighted

# 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})``````
Highlighted

# 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. Highlighted

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

@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

Highlighted

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

@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

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

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

Highlighted

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

@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

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

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

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

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