• 549K Members
• 6,765 Online
• 656K Conversations
SOLVED

## Help with a formula to sort dimensions?

Highlighted
Occasional Contributor

# 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 # Thick Width Length Grade/Description Units Unit Size Total Pieces 1630700 1X12-12 D GRADE WHITE 3 128 384 1320700 1X 6-10 D&BTR WHITE 1 256 256 1330700 1X 6-12 D&BTR WHITE 1 256 256 1220000 1X 4-10 ROUGH WHITE 4 200 800 1230000 1X 4-12 ROUGH WHITE 8 200 1,600

Cheers,

John

9 Replies
Highlighted

# Re: Help with a formula to sort dimensions?

@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

Highlighted
Solution

# Re: Help with a formula to sort dimensions?

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)

Highlighted

# Re: Help with a formula to sort dimensions?

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

Thanks,

John

Highlighted

# Re: Help with a formula to sort dimensions?

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!

Highlighted

# Re: Help with a formula to sort dimensions?

@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.

Highlighted

# Re: Help with a formula to sort dimensions?

@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.

Highlighted

# Re: Help with a formula to sort dimensions?

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)),"")

Highlighted

# Re: Help with a formula to sort dimensions?

@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!

Highlighted

# Re: Help with a formula to sort dimensions?

The pleasure is mine.