Forum Discussion

J-Griff's avatar
J-Griff
Copper Contributor
Oct 10, 2019
Solved

Help with a formula to sort dimensions?

Hello,

 

I am trying to determine which excel formula would be the best to create a spread sheet that will automatically input the dimensions for lumber into a given cell. The excerpt below shows the format I am attempting to achieve. In short, I need the first numerical figure in the "Grade/Description" column to automatically populate in the "Thick" column, the second numerical figure, followed by "X" to populate in the "Width" column, and finally, the last numerical figure, followed by "-" to populate in the "Length" column. Any tips or advice is greatly appreciated!

 

GSWP      Item #ThickWidthLengthGrade/DescriptionUnitsUnit SizeTotal Pieces
1630700   1X12-12 D GRADE WHITE 3128384
        
1320700   1X 6-10 D&BTR WHITE 1256256
        
1330700   1X 6-12 D&BTR WHITE 1256256
        
1220000   1X 4-10 ROUGH WHITE 4200800
        
1230000   1X 4-12 ROUGH WHITE82001,600
 

Cheers,

 

John

 
excel
Formulas and Functions
  • Twifoo's avatar
    Twifoo
    Oct 10, 2019

    Hi J-Griff,

    In the attached file, the formula for Thick in B2 is: 

    =LEFT($E2,
    FIND("x",$E2)-1)

    Moreover, the formula for Width in C2 is: 

    =MID($E2,
    FIND("x",$E2)+1,
    FIND("-",$E2)-FIND("x",$E2)-1)

    Finally, the formula for Length in D2 is: 

    =MID($E2,
    FIND("-",$E2)+1,
    FIND(" ",$E2)-FIND("-",$E2)-1)

9 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Oct 10, 2019

    Hi J-Griff,

    In the attached file, the formula for Thick in B2 is: 

    =LEFT($E2,
    FIND("x",$E2)-1)

    Moreover, the formula for Width in C2 is: 

    =MID($E2,
    FIND("x",$E2)+1,
    FIND("-",$E2)-FIND("x",$E2)-1)

    Finally, the formula for Length in D2 is: 

    =MID($E2,
    FIND("-",$E2)+1,
    FIND(" ",$E2)-FIND("-",$E2)-1)

    • J-Griff's avatar
      J-Griff
      Copper Contributor
      Oct 10, 2019

      Twifoo, Thank you so much for your help! I was able to get the first line to work with all three formulas you provided. And as you can see in the attached excel document, the first two columns, thick & width, work perfectly, but I can't figure out why the subsequent lines in the length column are not computing correctly? 

       

      Any advice? 

       

      Thanks,

       

      John

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        Oct 10, 2019

        J-Griff 

        Except for F7 and F43, I noticed that the entries under Column F includes a space character (" ") after the "X" character. The pattern of the entries under Column F must be uniform!

  • Smitty Smith's avatar
    Smitty Smith
    Iron Contributor
    Oct 10, 2019

    J-Griff I'd build a lookup table that you can reference. 

     

    Thick Width Length Grade/Description
    1 12 12 1X12-12 D GRADE WHITE 
    1 6 10 1X 6-10 D&BTR WHITE 
    1 6 12 1X 6-12 D&BTR WHITE 
    1 4 10 1X 4-10 ROUGH WHITE 
    1 4 12

    1X 4-12 ROUGH WHITE

     

    You can set it up as an Excel table, and use a Data Validation drop-down list to select the Grade/Description. Then use INDEX/MATCH to populate the Thick | Length |Width fields.

     

    HTH

Resources