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