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
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies