Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a cell that has a fairly archaic String. (It's the mana cost of a Magic: the Gathering spell.) Examples are 3g, 2gg, 3ur, and bg. There are 5 possible letters (g w u b r). I have 5 columns and would like to count at the bottom how many of each it contains. So my spreadsheet might look like this

  A                   B        C  D  E  F  G
1|Name                Cost     G  W  U  B  R
2|Centaur Healer      1gw      1  1  0  0  0
3|Sunspire Griffin    1ww      0  1  0  0  0 // just 1, even though 1ww
4|Rakdos Shred-Freak  {br}{br} 0  0  0  1  1

Basically, I want something that looks like =if(contains($A2,C$1),1,0) and I can drag it across all 5 columns and down all 270 some cards. (Those are actual data, by the way. It's not mocked :-) .)

In Java I would do this:

String[] colors = { "B", "G", "R", "W", "U" };
for(String color : colors) {
    System.out.print(cost.toUpperCase().contains(color) ? 1 : 0);

Is there something like this in using Excel 2010.

I tried using find() and search() and they work great if the color exists. But if the color doesn't exist, it returns #value - so I get 1 1 #value #value #value instead of 1 1 0 0 0 for, example, Centaur Healer (row 2). The formula used was if(find($A2,C$1) > 0, 1, 0).

share|improve this question
up vote 2 down vote accepted

Something along the lines of =IF(ISERROR(FIND("text", A1)), "false", "true") should work.

If you need to use wildcards, you can try search instead of find.

share|improve this answer
This is what I ended up using, except I used search instead of find. Still unsure what the difference is, but it works for my purposes. – corsiKa Sep 21 '12 at 21:44
From what I can see and as I mentioned above, wildcard support seems to be the chief (only?) difference between the two. BTW, another variant that might come in handy: =IFERROR(FIND("text", A1), "error") – Karan Sep 21 '12 at 21:47
In addition to wildcard support, SEARCH is not case sensitive, while FIND is. – chris neilsen Sep 21 '12 at 22:58

I could be wrong, but I believe you are looking for find().

FIND( substring, string, [start_position] )
share|improve this answer
I tried find but if it doesn't exist, I don't have a value to compare it to. Instead of 0 1 0 0 0 I get #VALUE 1 #VALUE #VALUE #VALUE – corsiKa Sep 21 '12 at 21:35
@corsiKa: Check out… as you might be able to pull something off by using IFERROR. Sometimes #VALUE could also mean the column isn't wide enough to display it... – Tom Wijsman Sep 21 '12 at 21:38
Tom that's excellent. I did =IFERROR(IF(SEARCH(K$1,$E2)>0,1,0),0) and it worked perfectly. (the K$1,$E2 there is the equivalent of $A2,C$1 in my example. – corsiKa Sep 21 '12 at 21:42

This will do:


share|improve this answer
btw the advantage with search is that you don't have to bother about the case... And if you want to change later that 1 and 0 to something else, this is better: =IF(ISERR(SEARCH(C$1,$B2)),0,1) – Alberto Sep 21 '12 at 22:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .