Hello all,
New to the forums here  in fact, brand new to EDH. Since I wanted to make a spreadsheet for my decklist that I'm making that contains lots of information on the cards, I figured I would start my forum posting off by providing something useful.
Here's an Excel Formula (made in Excel 2007) that reads a mana cost in a cell (referenced as F15 throughout this formula) that is entered in standard format (order of letters don't matter):
e.g: UU, 1UU, WUBRG, WBRGU (this is nonstandard order, but is still accepted), 3WBRGU (same here), 5WWUBBRGG
you get the point.
=IF(LEN(F15)=1,IF(IFERROR(ISNUMBER(F15),0),F15,1),IF(IFERROR(FIND("XX",F15,1),0),IF(ISNUMBER((TEXT(MID(F15,3,1),"0")*1)),LEN(MID(F15,4,LEN(F15)3))+TEXT(MID(F15,3,1),"0"),LEN(MID(F15,3,LEN(F15)2))),IF(LEFT(F15,1)="X",IF(ISNUMBER((TEXT(MID(F15,2,1),"0")*1)),MID(F15,2,1)+(LEN(MID(F15,3,LEN(F15)2))),LEN(MID(F15,2,LEN(F15)1))),IF(IFERROR(ISNUMBER(LEFT(F15,2)*1),0),LEFT(F15,2)+LEN(MID(F15,3,LEN(F15)2)),IF(IFERROR(ISNUMBER(LEFT(F15,1)*1),0),LEN(MID(F15,2,LEN(F15)1))+TEXT(LEFT(F15,1),"0"),LEN(F15))))))
Now off to look for deck ideas!
Edit: Changed the formula to capture values including X and equal to 0 properly. Edit2: Changed to accommodate 2digit numbers in casting costs. Edit3: Changed to accommodate XX costs.
Hint  to avoid having to edit the formula, just paste the formula above into cell G15 and it will already be pointing to its neighbor on the left (then copy cells from there). Or however your stuff is oriented.
Last edited by vorketh on 2010Jul22 8:10 pm, edited 3 times in total.
