Mayberry MM values incorrect n Value Calculator

[crotex]

When I run the value calculator and save it to excel (NL only 12 team 5x5) approximately 30 of the Mayberry MM codes seem to be missing a digit (or two) which makes it impossible to calculate adjusted Mayberry values. See Dee Gordon and Tyler Pastornicky for examples. Can someone please take a look at that? Thanks.

[RAY@HQ]

Sounds like players with leading zeroes in their MM codes getting truncated due to Excel formatting. I think if you change the MM code field to a number format, they come back. Just a theory.

[PD@HQ]

Yes, Ray is right. The leading zero in a 0335 code is dropped. Although in the original output, that cell is a text cell because of the space and the textual values for the Rel codes. If you're splitting the codes out, you might be causing the issue that way. You should try either using the "fixed width" method for busting the digits out or the formula "=VALUE(mid(G2,1,1))" where G2 is the cell with the MM Code in it. Then you can copy the formula across and just replace the first "1" with "2," "3" and so on to catch the second value, third value, etc. Using the VALUE formula makes the text-value of the digit into a numerical value, in case you need to use them for math.

[crotex]

Got it figured out. Thanks.

The cell comes across as text, but when I split the codes off the remaining cell converts to number format and the leading zeroes are dropped. About all i can do is convert that column back to text format, sort on that column, then hand re-enter the missing zeroes. Now I have a column where every cell has 4 digits, and i can split it using fixed width. Kind of a pain, but it can be done.

[RAY@HQ]

Sorry crotex, but it is a limitation of CSV, little if anything we can do about it at this end.

[PD@HQ]

The cell comes across as text, but when I split the codes off the remaining cell converts to number format and the leading zeroes are dropped. About all i can do is convert that column back to text format, sort on that column, then hand re-enter the missing zeroes. Now I have a column where every cell has 4 digits, and i can split it using fixed width. Kind of a pain, but it can be done.

Or you could follow the (much easier) process in post #3 using the VALUE function.