SOLVED
Home

Help with a formula to sort dimensions?

%3CLINGO-SUB%20id%3D%22lingo-sub-906088%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-906088%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20determine%20which%20excel%20formula%20would%20be%20the%20best%20to%20create%20a%20spread%20sheet%20that%20will%20automatically%20input%20the%20dimensions%20for%20lumber%20into%20a%20given%20cell.%20The%20excerpt%20below%20shows%20the%20format%20I%20am%20attempting%20to%20achieve.%20In%20short%2C%20I%20need%20the%20first%20numerical%20figure%20in%20the%20%22Grade%2FDescription%22%20column%20to%20automatically%20populate%20in%20the%20%22Thick%22%20column%2C%20the%20second%20numerical%20figure%2C%20followed%20by%20%22X%22%20to%20populate%20in%20the%20%22Width%22%20column%2C%20and%20finally%2C%20the%20last%20numerical%20figure%2C%20followed%20by%20%22-%22%20to%20populate%20in%20the%20%22Length%22%20column.%20Any%20tips%20or%20advice%20is%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EGSWP%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Item%20%23%3C%2FTD%3E%3CTD%3EThick%3C%2FTD%3E%3CTD%3EWidth%3C%2FTD%3E%3CTD%3ELength%3C%2FTD%3E%3CTD%3EGrade%2FDescription%3C%2FTD%3E%3CTD%3EUnits%3C%2FTD%3E%3CTD%3EUnit%20Size%3C%2FTD%3E%3CTD%3ETotal%20Pieces%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1630700%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1X12-12%20D%20GRADE%20WHITE%26nbsp%3B%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E128%3C%2FTD%3E%3CTD%3E384%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1320700%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1X%206-10%20D%26amp%3BBTR%20WHITE%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E256%3C%2FTD%3E%3CTD%3E256%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1330700%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1X%206-12%20D%26amp%3BBTR%20WHITE%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E256%3C%2FTD%3E%3CTD%3E256%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1220000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1X%204-10%20ROUGH%20WHITE%26nbsp%3B%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E800%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1230000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1X%204-12%20ROUGH%20WHITE%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E1%2C600%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-906088%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-906637%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-906637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3BI'd%20build%20a%20lookup%20table%20that%20you%20can%20reference.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20width%3D%22353%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2264%22%3EThick%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%3EWidth%3C%2FTD%3E%0A%3CTD%20width%3D%2264%22%3ELength%3C%2FTD%3E%0A%3CTD%20width%3D%22161%22%3EGrade%2FDescription%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3CTD%3E12%3C%2FTD%3E%0A%3CTD%3E12%3C%2FTD%3E%0A%3CTD%3E1X12-12%20D%20GRADE%20WHITE%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3CTD%3E6%3C%2FTD%3E%0A%3CTD%3E10%3C%2FTD%3E%0A%3CTD%3E1X%206-10%20D%26amp%3BBTR%20WHITE%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3CTD%3E6%3C%2FTD%3E%0A%3CTD%3E12%3C%2FTD%3E%0A%3CTD%3E1X%206-12%20D%26amp%3BBTR%20WHITE%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3CTD%3E4%3C%2FTD%3E%0A%3CTD%3E10%3C%2FTD%3E%0A%3CTD%3E1X%204-10%20ROUGH%20WHITE%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E1%3C%2FTD%3E%0A%3CTD%3E4%3C%2FTD%3E%0A%3CTD%3E12%3C%2FTD%3E%0A%3CTD%3E%3CP%3E1X%204-12%20ROUGH%20WHITE%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20set%20it%20up%20as%20an%20Excel%20table%2C%20and%20use%20a%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FCreate-a-drop-down-list-7693307A-59EF-400A-B769-C5402DCE407B%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EData%20Validation%20drop-down%3C%2FA%3E%20list%20to%20select%20the%20Grade%2FDescription.%20Then%20use%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fbasic-index-match-exact%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EINDEX%2FMATCH%3C%2FA%3E%20to%20populate%20the%20Thick%20%7C%20Length%20%7CWidth%20fields.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-906787%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-906787%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20for%20Thick%20in%20B2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DLEFT(%24E2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EFIND(%22x%22%2C%24E2)-1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EMoreover%2C%20the%20formula%20for%20Width%20in%20C2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMID(%24E2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EFIND(%22x%22%2C%24E2)%2B1%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EFIND(%22-%22%2C%24E2)-FIND(%22x%22%2C%24E2)-1)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EFinally%2C%20the%20formula%20for%20Length%20in%20D2%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DMID(%24E2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EFIND(%22-%22%2C%24E2)%2B1%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EFIND(%22%20%22%2C%24E2)-FIND(%22-%22%2C%24E2)-1)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907104%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907104%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%2C%26nbsp%3BThank%20you%20so%20much%20for%20your%20help!%20I%20was%20able%20to%20get%20the%20first%20line%20to%20work%20with%20all%20three%20formulas%20you%20provided.%20And%20as%20you%20can%20see%20in%20the%20attached%20excel%20document%2C%20the%20first%20two%20columns%2C%20thick%20%26amp%3B%20width%2C%20work%20perfectly%2C%20but%20I%20can't%20figure%20out%20why%20the%20subsequent%20lines%20in%20the%20length%20column%20are%20not%20computing%20correctly%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907111%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcept%20for%20F7%20and%20F43%2C%20I%20noticed%20that%20the%20entries%20under%20Column%20F%20includes%20a%20space%20character%20(%22%20%22)%20after%20the%20%22X%22%20character.%20The%20pattern%20of%20the%20entries%20under%20Column%20F%20must%20be%20uniform!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907145%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907145%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bi%20see%20what%20you%20mean%2C%20but%20unfortunately%2C%20I%20can't%20change%20the%20pattern%20for%20the%20entries%20in%20column%20f%20because%20they%20are%20being%20populated%20from%20the%20data%20entry%20tab.%20I%20presume%20that%20extra%20space%20after%20the%20%22X%22%20is%20because%20the%20the%20figure%20goes%20from%20being%20two%20digits%20to%20a%20single%20digit.%20Either%20way%2C%20apart%20from%20manually%20altering%20each%20line%2C%20there%20is%20no%20way%20to%20change%20the%20format%20before%20the%20data%20is%20exported%20into%20excel.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907147%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3Bis%20there%20a%20way%20to%20add%20the%20second%20condition%20to%20the%20formula%20in%20order%20to%20capture%20the%20same%20data%3F%20Perhaps%20if%20we%20incorporated%20an%20IF%20formula%3F%20I%20am%20just%20spit%20balling%20here%2C%20I%20have%20no%20clue%20how%20that%20would%20look.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907268%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20version%20of%20your%20file%2C%20I%20wrapped%20all%20formulas%20with%20IFERROR.%20Given%20your%20explanation%2C%20I%20found%20it%20operose%20to%20ascertain%20the%20apposite%20formula.%20Nonetheless%2C%20I%20unearthed%20this%20formula%20in%20F7%20for%20you%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(MID(%24F7%2CFIND(%22-%22%2C%24F7)%2B1%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(10%2C--MID(%24F7%2CROW(%24A%241%3AINDEX(%24A%3A%24A%2CLEN(%24F7)))%2C1)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EROW(%24A%241%3AINDEX(%24A%3A%24A%2CLEN(%24F7))))-FIND(%22-%22%2C%24F7))%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907616%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThank%20you%20for%20all%20your%20help.%20I%20was%20able%20to%20build%20upon%20your%20first%20set%20of%20formulas%20to%20achieve%20my%20desired%20result.%20By%20adding%20a%20another%20column%20and%20applying%20the%20formula%2C%26nbsp%3B%3DSUBSTITUTE(SUBSTITUTE(G7%2C%22X%20%22%2C%22X%22)%2C%22-%20%22%2C%22-%22)%20to%20the%20Grade%2FDescription%20column%2C%20I%20was%20able%20to%20eliminate%20the%20formatting%20discrepancies%20which%20allowed%20your%20original%20formulas%20to%20work%20perfectly.%20Thanks%20again%20for%20all%20your%20help!%20I%20would%20not%20have%20been%20able%20to%20figure%20it%20out%20without%20you!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-907695%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20formula%20to%20sort%20dimensions%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-907695%22%20slang%3D%22en-US%22%3EThe%20pleasure%20is%20mine.%3C%2FLINGO-BODY%3E
J-Griff
Occasional Contributor

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

 
9 Replies

@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

Solution

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)

@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

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

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

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

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

The pleasure is mine.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies