Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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);
    System.out.print("\t");
}

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

3 Answers 3

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
1  
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
1  
@corsiKa: Check out stackoverflow.com/questions/9513795/… 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:

=N(NOT(ISERR(SEARCH(C$1,$B2))))

share|improve this answer
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.