Excel Formula to return cell value with matching text string

July 26, 2016 at 10:18:34
Specs: Windows 7
I am working on an excel formula that will return the value of the cell if the text string matches. the problem I am having is the blank cells where the text does not match. How do I update this formula to not have blank cells but only return the list that matches thee text string? here is my formula. This returns the correct response but I will have 5-10 cells with blank cells that fall between the correct response. The data is in one list in Column A

=IF(ISNUMBER(SEARCH("Cheese",A8)),A8,IF(ISNUMBER(SEARCH("Jack",A8)),A8,""))


See More: Excel Formula to return cell value with matching text string

Report •


#1
July 26, 2016 at 12:45:02
the problem I am having is the blank cells where the text does not match.

Not sure what you mean by this.
When I ran your formula, if cell A8 is Blank, the formula returned nothing.
Are you saying you receive an error message of some type or incorrect
data?

Perhaps if you posted a small sample of your data, after reading this HOW-TO
which explains the use of the < PRE > tags in aligning your data:

http://www.computing.net/howtos/sho...

Please use Column Letters and Row Numbers.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
July 26, 2016 at 13:11:28
I do not get error messages I just don't want any blank cells. If I have a list in row one column A:
Beef
American Cheese
Lettuce
Cheddar cheese

If I were to use my current formula in cell C2 going down
the list would show the following:
C2 =
C3 = American Cheese
C4 =
C5 = Cheddar Cheese

I do not want Cells C2 and C4 to return blank instead I want American Cheese to show up in C2 and Cheddar Cheese to show in C3.


Report •

#3
July 26, 2016 at 14:38:16
If I understand you correctly you want to SEARCH() a Range of Cells, A1 thru A4,
not simply the adjacent cell, correct?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 26, 2016 at 14:46:56
Correct there is a list A3:A28 some of the cells contain the word cheese I want to copy those cells using a formula like I have above that only returns that matching value cells content. So if the Cell A4 says American Cheese and cell A5 says Beef I only want it to return American Cheese and not a blank value for Beef.

Report •

#5
July 26, 2016 at 15:06:32
Still a bit confused.

You have two key words your searching for: Cheese and Jack

If the adjacent cell does not contain your key word, you simply want Cheese?

What is it your trying to do? It does not seem to make any sense to me.

With your data like:

          A
1) Beef
2) American Cheese
3) Lettuce
4) Cheddar cheese 

You want something like:

          A         B          C
1) Beef                  Cheese
2) American Cheese       American Cheese
3) Lettuce               Cheese
4) Cheddar cheese        Cheddar cheese 

If that is correct, then change your formula like:

=IF(ISNUMBER(SEARCH("Cheese",A1)),A1,IF(ISNUMBER(SEARCH("Jack",A1)),A1,"Cheese"))

MIKE

http://www.skeptic.com/


Report •

#6
July 26, 2016 at 15:28:12
Here is the full list. As you can see Pepper Jack is a choice. I am trying to develop a formula to move these into a meaningful list, ie Meats,Cheeses, toppings etc. I started with the Cheese. All I need is the formula to only pull out cheeses. Ie beef is not a cheese so I don't want it to return blank or cheese or anything it should move to the next selection until it hits another cheese. If I copy the formula down. the final list should say
American Cheese
Cheddar Cheese
Mozzerella Cheese
Pepper Jack

Nothing else in the cheese column. No blanks No just cheese etc.. just the matching cheeses from the list below.

Meats
Beef
Black Bean
Buffalo Chicken
Grilled Chicken
Soy Burger
Turkey
Veggie Burger

Cheese
American Cheese
Cheddar Cheese
Mozzarella Chees
Pepper Jack

Toppings
Jalapenos
Lettuce
Mushrooms
Onions
Peppers
Pickles
Spinach
Tomato
Bacon

Bread
Gluten Free Bun
Multigrain Bun
Onion Roll
Potato Roll
Ramen Bun
Whole Wheat Bun


Report •

#7
July 26, 2016 at 15:50:03
Why not just use your formula, then simply Sort the column.
All the data is sorted to the top of the column, you have no blanks,
and you have your list.

I don't expect this to be more than a one time operation, correct?

MIKE

http://www.skeptic.com/


Report •

#8
July 26, 2016 at 17:53:44
It is a potential changing list. I am using the burger methodology as an example. The problem with sorting is that this formula will be applied to other columns so blanks in every column would not make for easier sorting. I think index might work but not sure how to best incorporate in the above.

Report •

#9
July 26, 2016 at 18:18:45
The problem with sorting is that this formula will be applied to other columns so blanks in every column would not make for easier sorting.

You don't need to Sort to remove blank cells from all your columns.
Removing blank cell is relatively easy.
Try this:


Press F5
Click the Special button
Select Blanks
Click OK
On the Ribbon, Select Delete ( in the Cells section)
Select Delete Cells
Select Shift Cells Up
Click OK

MIKE

http://www.skeptic.com/


Report •

#10
July 26, 2016 at 18:37:37
This did not work as the that does not consider the cell blank when using the formula.

Report •

#11
July 27, 2016 at 12:12:38
I'll keep working on this, but I've had no luck as of yet, perhaps someone
else will jump in with something.

Best option, for now, is to use your formula, then sort the column.

MIKE

http://www.skeptic.com/


Report •


Ask Question