Sunday, June 5, 2011

yogi_Separate Numeric Digit And Specified Letter From Stirngs And TotalUp

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
mtvernon said:
A cell contains both letters and numbers. How do I pull the number into a separate cell and the letter into yet another?
I'm new to spreadsheets in general and Google Spreadsheets in particular. I've looked all over these forums, but can't seem to find a solution to my problem. Here's the situation:


I have a cell that contains the cost of a resource in a card game. It's usually expressed using both a number and a letter (1W). Sometimes it's just a number (1). Other times, it's just a letter (W -- or U, B, R, G). To further complicate matters, variables appear from time to time (XW or, very rarely, X1W).


What I want is to take the cost column (1W, 1, W, XW, X1W, 3W, 8, WW, 2WW, XWW, et cetera) and break it up into additional columns representing each part. Then, I want to sum the parts into a number that'll indicate the "converted" cost. The cost 1W, for instance, should show a 1 under the column that represents numbers and a 1 under the column that counts letters other than X. It should sum up as 2. The cost 2WW should show a 2 under the column that represents numbers and a 2 under the column that counts letters other than X. It should sum up as 4. The cost XW should show a 0 under the column that represents numbers and a 1 under the column that counts letters other than X. The variable X should always be converted to 0. It should sum up as 1.
My question is, how in the world do I create a formula that sees 2WW and breaks it up as described above? I've tried writing the cost differently (1-W, 2-WW, X-W, et cetera) and pulling the number value left of the letter with =VALUE(LEFT(K2)). And I can count the number of letters using =LEN(K6)-LEN(SUBSTITUTE(K6,"W","")), but I'm not even sure what process is at work there; all I know is that it does work, at least so far. Then I just =SUM(L2:Q2) for the "converted" cost. Only, if there's an X left of the dash (X-WW), I get an error that says "Cannot parse text: X." Is there a way to have =VALUE(LEFT(K2)) always show a zero in place of X? And what of unusual cases in which the cost is X1W (X1-W or, possibly, XW or X0-W)? Perhaps =VALUE(LEFT(K2)) should simply not count X at all?
-------------------------------------------------------------------------