Calculable Text?

%3CLINGO-SUB%20id%3D%22lingo-sub-1415943%22%20slang%3D%22en-US%22%3ECalculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415943%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20possibility%20to%20turn%20the%20data%20in%20book%201%20into%20a%20calculation%3F%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22478%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22277%22%3EBourbon%20Vanilla%20%3A%20Dairy%20Ice%20Cream%3C%2FTD%3E%3CTD%20width%3D%22201%22%3E2%20x%205%20ltr%20%5B2%5D%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%202%20x%205ltr%20should%20be%202*5ltr%26nbsp%3B%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CTABLE%20width%3D%22478%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22277%22%3EBloomer%20%3A%20Malted%20Brown%3C%2FTD%3E%3CTD%20width%3D%22201%22%3E7x900g%20%5B7%5D%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%207x900g%20should%20be%207*900g%3C%2FP%3E%3CP%3EThe%20Data%20coming%20from%20a%20third%20party%20as%20it%20is.%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20method%20to%20make%20this%20Text%20to%20calculable.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20time%26nbsp%3B%3C%2FP%3E%3CP%3EFrank%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1415943%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415965%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415965%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20are%20your%20expected%20results%20here%3F%20Do%20you%20simply%20want%20to%20replace%20%22x%22%20with%20%22*%22%3F%20Or%20do%20you%20want%20to%20see%20the%20total%20weight%2Fvolume%20returned%20to%20another%20cell%2C%20i.e.%20(for%20your%20two%20examples)%3A%20%3CSTRONG%3E10%20ltr%3C%2FSTRONG%3E%20and%26nbsp%3B%3CSTRONG%3E6300g%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415975%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415975%22%20slang%3D%22en-US%22%3EHi%20Jos%2C%3CBR%20%2F%3EBoth%20option%20is%20acceptable.%3CBR%20%2F%3EThanks%20Frank%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415981%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415981%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20the%20first%20is%20very%20simple%3A%20if%20cell%20A1%20contains%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E2%20x%205%20ltr%20%5B2%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20this%20formula%20in%20B1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DSUBSTITUTE(A1%2C%22x%22%2C%22*%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewill%20return%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E2%20*%205%20ltr%20%5B2%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415993%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415993%22%20slang%3D%22en-US%22%3EHi%20Jos%2C%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%20for%20this%2C%20but%20now%20how%20can%20i%20turn%20this%20into%20a%20total%20volume%20in%20an%20another%20cell%20what%20can%20see%20the%20difference%20between%20Liter%20and%20Kilogram%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3EFrank%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416007%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAh%2C%20so%20then%20that%20first%20solution%20was%20not%20in%20fact%20an%20option%20for%20you.%20Are%20you%20now%20saying%20that%20in%20fact%20my%20second%20proposal%20is%20what%20you%20require%2C%20i.e.%20to%20return%20%3CSTRONG%3E10%20ltr%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E6300g%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20can%20you%20clarify%20the%20precise%20nature%20of%20the%20cell%20contents%3F%20For%20example%2C%20do%20they%20%3CSTRONG%3Ealways%3C%2FSTRONG%3E%20have%20a%20number%20in%20square%20brackets%20at%20the%20end%2C%20e.g.%20%5B2%5D%2C%20%5B7%5D%3F%20Is%20this%20value%20to%20be%20taken%20into%20consideration%2C%20or%20ignored%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20what%20version%20of%20Excel%20are%20you%20using%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416026%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416026%22%20slang%3D%22en-US%22%3EHi%20Josh%3CBR%20%2F%3E%3CBR%20%2F%3ESorry%2C%3CBR%20%2F%3EThis%20some%20example%20without%20brackets%3CBR%20%2F%3EChicken%20Legs%201%20x11%20kg%3CBR%20%2F%3EChicken%20Fillet%20%3A%20150-180g%201x60each%3CBR%20%2F%3ERhubarb%20%3A%20Whole%20Fruit%201%20x%201%20kg%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20there%20is%20a%20value%20in%20the%20bracket%20that%20referral%20to%20the%20pack%20not%20important%20nut%20if%20it%20helps%20you%20can%20use.%3CBR%20%2F%3EIf%20I'm%20able%20to%20get%20organized%20the%20majority%20of%20the%20cells%20I'm%20ok%3CBR%20%2F%3EThanks%3CBR%20%2F%3EFrank%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416045%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416045%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20I%20think%20there's%20probably%20too%20much%20variation%20here%20for%20a%20single%20formula.%20For%20example%2C%20what's%20the%20expected%20result%20for%26nbsp%3B%3CSTRONG%3EChicken%20Fillet%20%3A%20150-180g%201x60each%3C%2FSTRONG%3E%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20you%20need%20to%20clarify%20whether%20each%20of%20the%20entries%20you%20give%20is%20in%20a%20single%20cell%20or%20not.%20For%20example%2C%20for%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EChicken%20Legs%201%20x11%20kg%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20this%20in%20two%20separate%20cells%2C%20or%20one%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20think%20it%20would%20help%20matters%20considerably%20if%20you%20posted%20an%20actual%20workbook%20containing%20a%20few%20examples%20together%20with%20expected%20results.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20you%20didn't%20say%20what%20version%20of%20Excel%20you're%20using.%20Office%20365%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416061%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416061%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3BJos.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%202010%20version%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20full%20data%20base%20for%20you.%3C%2FP%3E%3CP%3EMy%20target%20here%20to%20calculate%20a%20menu%20price%20based%20on%20this%20database.%3C%2FP%3E%3CP%3EFor%20the%20chicken%20fillet%20I%20just%20have%20to%20know%20the%20price%20of%201pc%3C%2FP%3E%3CP%3EFor%20milk%20I%20want%20to%20get%20%2Fliter%20price%3C%2FP%3E%3CP%3EIf%20you%20can%20show%20me%20the%20path%20to%20follow%20that%20would%20be%20great.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EFrank%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416109%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreat%2C%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20row%202%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23333399%22%3E%3CSTRONG%3E%3DIF(COUNT(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CH2))%2CPRODUCT(0%2BMID(SUBSTITUTE(LEFT(H2%2CFIND(I2%2CH2)-1)%26amp%3B%22x1x1x1x1x1%22%2C%22x%22%2CREPT(%22%20%22%2C25))%2C25*%7B0%2C1%2C2%2C3%2C4%2C5%7D%2B1%2C25))%26amp%3BLOOKUP(1%2C0%2FCOUNTIF(H2%2C%22*%22%26amp%3B%7B%22ltr%22%2C%22g%22%2C%22kg%22%2C%22ml%22%2C%22each%22%2C%22portions%22%7D%26amp%3B%22*%22)%2C%22%20%22%26amp%3B%7B%22ltr%22%2C%22g%22%2C%22kg%22%2C%22ml%22%2C%22each%22%2C%22portions%22%7D)%2C%22N%2FA%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20copied%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22N%2FA%22%20is%20returned%20if%20the%26nbsp%3B%3CEM%3EPack%20Description%20%3C%2FEM%3Ecolumn%20does%20not%20contain%20any%20numerical%20values%20(e.g.%20rows%2C%2028%2C%2034%2C%2039%2C%20101%2C%20169%2C%20175%2C%20180%20and%20208).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you're%20not%20using%20an%20English%20language-version%20of%20Excel%20then%20you%20may%20need%20some%20help%20translating%20the%20above%3A%20although%20online%20tools%20are%20able%20to%20translate%20function%20names%2C%20they%20are%20not%20able%20to%20correctly%20translate%20separators%20within%20array%20constants%2C%20e.g.%26nbsp%3B%7B0%2C1%2C2%2C3%2C4%2C5%7D%20into%20the%20locale%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20and%20get%20back%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416147%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416147%22%20slang%3D%22en-US%22%3E%3CP%3EFYI%20I've%20replied%20with%20a%20solution%20but%20the%20admin%20at%20this%20site%20are%20withholding%20that%20post%20as%20part%20of%20their%20auditing.%20They%20say%20it%20will%20be%20released%20within%2024%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416150%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416150%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Josh%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20big%20Thank%20you%20for%20your%20help%20and%20your%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehave%20Good%20day%3C%2FP%3E%3CP%3EFrank%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416151%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20case%2C%20there%20was%20an%20inaccuracy%20in%20that%20solution%2C%20so%20I%20see%20no%20harm%20in%20posting%20a%20corrected%20version%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20row%202%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23333399%22%3E%3CSTRONG%3E%3DIF(COUNT(FIND(%7B0%2C1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%2C9%7D%2CH2))%2CPRODUCT(0%2BMID(SUBSTITUTE(LEFT(H2%2CMIN(FIND(%7B%22ltr%22%2C%22kg%22%2C%22g%22%2C%22ml%22%2C%22each%22%2C%22portions%22%7D%2CH2%26amp%3B%22ltrkggmleachportions%22)-1))%26amp%3B%22x1x1x1x1x1%22%2C%22x%22%2CREPT(%22%20%22%2C25))%2C25*%7B0%2C1%2C2%2C3%2C4%2C5%7D%2B1%2C25))%26amp%3BLOOKUP(1%2C0%2FFIND(%7B%22ltr%22%2C%22g%22%2C%22kg%22%2C%22ml%22%2C%22each%22%2C%22portions%22%7D%2CH2)%2C%22%20%22%26amp%3B%7B%22ltr%22%2C%22g%22%2C%22kg%22%2C%22ml%22%2C%22each%22%2C%22portions%22%7D)%2C%22N%2FA%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20copied%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416157%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416157%22%20slang%3D%22en-US%22%3EHi%20Jos%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20is%20looking%20awesome%20and%20it%20is%20working!%20You%20are%20a%20star.%20Thanks%3CBR%20%2F%3EFrank%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416168%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416168%22%20slang%3D%22en-US%22%3EThat%20is%20just%20fine%2C%20Even%20if%20i'm%20just%20a%20chef%20i%20might%20be%20able%20to%20manage%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3EIf%20not%20i'll%20run%20back%20to%20you%20if%20that%20is%20ok%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3EFrank%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416158%22%20slang%3D%22en-US%22%3ERe%3A%20Calculable%20Text%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F678868%22%20target%3D%22_blank%22%3E%40Qkta6667%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGlad%20to%20hear%20it!%20Just%20be%20aware%20that%20the%20formula%20only%20accounts%20for%20the%20specific%20units%20of%20measurement%20that%20I%20found%20within%20your%20file%2C%20i.e.%20%22ltr%22%2C%22kg%22%2C%22g%22%2C%22ml%22%2C%22each%22%2C%22portions%22%2C%20and%20would%20need%20expanding%20to%20include%20any%20other%20units%20of%20measurement%20that%20may%20crop%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi Guys,

 

Is there a possibility to turn the data in book 1 into a calculation?

Example: 

Bourbon Vanilla : Dairy Ice Cream2 x 5 ltr [2]

The 2 x 5ltr should be 2*5ltr 

or

Bloomer : Malted Brown7x900g [7]

The 7x900g should be 7*900g

The Data coming from a third party as it is. 

Any method to make this Text to calculable.

 

Thanks for your time 

Frank 

 

 

 

 

 

 

 

22 Replies
Highlighted

@Qkta6667 

 

For Quantity, this formula in I2:

 

=IFERROR(PRODUCT(0+MID(SUBSTITUTE(LEFT(H2,MATCH(1,INDEX(-MID(LEFT(H2,FIND("[",H2&"[")-1),ROW($1:$99),1),)))&"x1x1x1x1x1","x",REPT(" ",25)),25*{0,1,2,3,4,5}+1,25)),"N/A")

 

For Units, this formula in J2:

 

=IF(I2="N/A","N/A",TRIM(RIGHT(LEFT(H2,FIND("[",H2&"[")-1),LEN(LEFT(H2,FIND("[",H2&"[")-1))-MATCH(1,INDEX(-MID(LEFT(H2,FIND("[",H2&"[")-1),ROW($1:$99),1),)))))

 

The above will work for any units of measurement.

 

Regards

Highlighted

Hi @Jos_Woolley 

 

Thank you very much. It is brilliant.

 

With your and @Sergei Baklan help I can finish my "homework". 

And I have to admit I learned a lot about Excel with your formula.

Thanks

Kind Regards

 

Frank

Highlighted

@Qkta6667 

 

Hi Frank,

 

Glad to hear it!

 

Cheers,

 

Jos