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?
-------------------------------------------------------------------------

6 comments:

  1. Yogi, I'm sorry to bother you again, but I can't figure out how to manipulate the formulas you showed me into what I need. I've posted a link to the troubleshooting spreadsheet and explained my issue there. Hope you can help!

    Best,
    mtvernon.

    https://spreadsheets.google.com/spreadsheet/ccc?key=0AjRN9iKbODuRdGFGc09hcEgyY3doQ3lweHN5M2ZRV3c&hl=en_US

    ReplyDelete
  2. Hi mtvernon:

    When I had keyed-in the formulas in your spreadsheet, some how I had switched the formulas in columns L and Q. I had it however right in my Blog.

    I have now corrected the formulas in your spreadsheet with a clarification in the header for count of Ws and Numerics in column K.

    Please check it out and see if it works for you now.

    Cheers!
    Yogi

    ReplyDelete
  3. I see! Thanks so much. You're a wizard...

    Best,
    mtvernon.

    ReplyDelete
  4. Okay, I've seen what you added in Sheet3 above. The U, B, R, and G columns are properly counting the appearances of their respective letters. But, when I move over to the next spreadsheet in my original document -- the one focused on U -- the CMC column (spreadsheet column R) isn't counting what the U column (spreadsheet column M) shows. So my total in spreadsheet column R is incorrect. But I can't figure out for the life of me why this is!

    https://spreadsheets.google.com/spreadsheet/ccc?key=0AjRN9iKbODuRdGFGc09hcEgyY3doQ3lweHN5M2ZRV3c&hl=en_US

    Thanks again for any help you can provide,
    mtvernon.

    ReplyDelete
  5. Okay, nevermind. I seem to have figured it out. Couldn't have done so without you!

    Much appreciated,
    mtvernon.

    ReplyDelete
  6. Hi mtvernon:

    If you have figured it out, then you are all set.

    However, let me add the following ...
    Please note that I have indicated that CMC column is the sum of columns for Ws and Numerics as you had originally wanted. Then later I added the count for other letters as well. I don't know what this is about and what columns you are summing and why you are summing them.

    So, if you need CMC column to sum counts from more that just W column and Numberics column, just modify the formula in the CMC column accordingly.


    I hope this helps.

    Cheers!
    Yogi

    ReplyDelete