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 t...
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)

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!

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

    Twifoo is there a way to add the second condition to the formula in order to capture the same data? Perhaps if we incorporated an IF formula? I am just spit balling here, I have no clue how that would look.

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

    Twifoo i see what you mean, but unfortunately, I can't change the pattern for the entries in column f because they are being populated from the data entry tab. I presume that extra space after the "X" is because the the figure goes from being two digits to a single digit. Either way, apart from manually altering each line, there is no way to change the format before the data is exported into excel.  

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

      J-Griff 

      In the attached version of your file, I wrapped all formulas with IFERROR. Given your explanation, I found it operose to ascertain the apposite formula. Nonetheless, I unearthed this formula in F7 for you: 

      =IFERROR(MID($F7,FIND("-",$F7)+1,
      LOOKUP(10,--MID($F7,ROW($A$1:INDEX($A:$A,LEN($F7))),1),
      ROW($A$1:INDEX($A:$A,LEN($F7))))-FIND("-",$F7)),"")

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

        Twifoo Thank you for all your help. I was able to build upon your first set of formulas to achieve my desired result. By adding a another column and applying the formula, =SUBSTITUTE(SUBSTITUTE(G7,"X ","X"),"- ","-") to the Grade/Description column, I was able to eliminate the formatting discrepancies which allowed your original formulas to work perfectly. Thanks again for all your help! I would not have been able to figure it out without you! 

Resources