google sheets fractions

[andy photenas]

Hello all, I am trying to use google sheets to cut all of my doors and drawer faces in my shop.

For anyone that does not know its a free version of excel that can take excel files and convert/open them in google sheets. After that has been done you can use sheets similar to excel.

My question is this ,

Can you sort columns by the size of the number? (seems a dumb question but i cant seem to find a command that actually does the sorting)


If you can sort by the size of the number can you still do this when the numbers are in fractional format?


What I am able to get is close but not right, the only command so far that i can get to do any sorting is the A-Z command. but it only uses the first number on the left. in other words it lists 2 right after 19 .

Does anyone use the export to excel command and then build doors from a spreadsheet? Please let me know how you deal with this issue if anyone knows .

Thanks again my fellow woodworkers!

[Steve Wurster]

The problem is probably that you entered the values as straight-out mixed fractions (e.g. 5 5/8), but when you do that Google Sheets thinks that's a string and not a number.

Instead, you have to enter that as a decimal number (in this example, 5.625). At the same time, be sure to set the column's formatting to Format -> Number -> More Formats -> Custom Number Format -> # ??/?? The sheet will then display the values as mixed fractions, but if you click on any number you'll see the decimal equivalent. Sorting then works properly.

[andy photenas]

I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
Thanks a ton for trying to help me out with this !

[Tim M Tuttle]

I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
Thanks a ton for trying to help me out with this ! You would have to write a custom formula to convert. Or you could break the column into two columns (whole number and fraction) and then combine to make it a decimal number. My recommendation is the latter but going forward, always enter numbers as decimal numbers then you can change the way they display.

[Steve Wurster]

I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
Thanks a ton for trying to help me out with this !

As Tim also pointed out, it's best to enter the numbers as decimals (5.625 for that example) and then just use the formatting menu to change how it's displayed.

[Bill Carey]

I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
Thanks a ton for trying to help me out with this !


Just curious what the end game is here. Are you trying to make a cut list for someone in the shop? Are you using a drawing / design program to layout the kitchens and cabinets? If so, you should be able to export to excel or sheets or pdf , etc. You said you are trying to do it without entering numbers which to me means you have the info somewhere already and are transcribing to sheets. Like I said, just curious.

[Greg Funk]

I am trying to do this without entering any numbers . I find every time i do this many i mess a few of them up . I am building large kitchens so i have over 300 parts on each of these lists. (and thats just door/drawer parts)
Can that format command you mention work if the 5 5/8 number is there and then you do the formatting you explained?
I am really new to spreadsheets in general so i am learning as i go, and there seems a lot to learn on google sheets!
Thanks a ton for trying to help me out with this !Where are you getting the data for your spreadsheet if you're not entering it? Are you using a CAD program that is generating a cutlist?

If you want to enter dimensions in fractional form you probably need to use excel. With Google sheets you need to enter the numbers in decimal form and then you can format them as fractions but that is more of a hassle if you want to work in fractions. With excel you can enter a number as a fraction and it will recognize the fraction as a number. It can then display it in either decimal or fractional form.

[David L Morse]

As Tim suggested, dedicate an empty column next to your dimension list for a sorting key. Enter this formula in that column:

=left(A1,find(" ",A1)-1)+mid(A1,find(" ",A1)+1,find("/",A1)-find(" ",A1)-1)/right(A1,len(A1)-find("/",A1))

Where A1 is the cell you want to sort numerically.

If you have a good understanding of how cell addresses change when you copy a cell you can simply copy the above and paste it into a strategically chosen cell and then just copy and paste from there to wherever you want. Saves trying to type it in manually.

[andy photenas]

Sorry if i asked that in a confusing way, i thought it was common knowledge that if you design in eCAb and make your doors "build in house" that you can then bring up the cutlist when in floor plan view. From here at the top you can choose export excel file. I then upload this to google sheets and in doing so sheets changes the file to a google sheets file. after this you can use sheets almost exactly the same as excel and its free.
This is how i am not entering any numbers. the file comes up as fractional not decimal.

I was able to use all the ideas you guys had to figure it out !!!

The best way to do this is by making a second column as suggested but no formula is needed. all you need to do is use the split command under data tab and choose to split by " space" this breaks the fraction off of the whole numbers but keeps it in the column to the right . This can be done in one move if you highlight the column.

And to answer the question about how i use this list. I use it to build the doors in my shop but i send the file to an eCab friendly CnC shop near me. While i build the doors he makes the bodies.

Thanks again good woodworks !!