LAMBDA Examples: Count words in a text string

%3CLINGO-SUB%20id%3D%22lingo-sub-2061855%22%20slang%3D%22en-US%22%3ELAMBDA%20Examples%3A%20Count%20words%20in%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061855%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20post%20is%20the%20second%20of%20a%20series%20where%20we%20will%20be%20sharing%20out%20examples%20of%20lambdas.%20This%20is%20intended%20to%20highlight%20lambdas%20we%20have%20cooked%20up%20that%20show%20the%20power%20of%20this%20new%20super-charged%20function.%20Additionally%2C%20you%20have%20the%20opportunity%20to%20engage%20with%20us%20on%20the%20lambdas%20you%20have%20built%20yourself%20and%20of%20course%20let%20us%20know%20how%20our%20own%20formulas%20could%20be%20improved.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20didn%E2%80%99t%20catch%20the%20announcement%2C%20be%20sure%20to%20check%20out%20the%20blog%20post%20highlighting%20the%20release%20of%20this%20new%20function%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fannouncing-lambda-turn-excel-formulas-into-custom-functions%2Fba-p%2F1925546%22%20target%3D%22_blank%22%3ELAMBDA%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20today%E2%80%99s%20example%20we%20will%20be%20creating%20a%20lambda%20which%20allows%20you%20to%20count%20how%20many%20words%20are%20in%20a%20text%20string.%3C%2FP%3E%0A%3CP%3E%3CEM%3E%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3EI%20find%20myself%20making%20use%20of%20this%20formula%20all%20the%20time%20when%20I%20need%20to%20do%20text%20scrubbing%20or%20fact%20checking%20I%20have%20edited%20my%20data%20correctly.%20Hopefully%2C%20if%20you%E2%80%99re%20like%20me%20and%20find%20yourself%20making%20use%20of%20this%20formula%20from%20time%20to%20time%2C%20it%20gives%20you%20a%20more%20repeatable%20way%20to%20call%20it%20without%20having%20to%20write%20all%20the%20logic%20from%20scratch.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--552013089%22%20id%3D%22toc-hId--552012995%22%20id%3D%22toc-hId--552012995%22%20id%3D%22toc-hId--552012995%22%20id%3D%22toc-hId--552013089%22%3ECount%20words%20in%20a%20text%20string%3C%2FH2%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20this%20example%2C%20lets%20start%20with%20the%20problem%20we%20have%20at%20hand%20and%20would%20like%20to%20abstract%20as%20a%20custom%20function.%20Below%20you%20can%20see%20a%20series%20of%20text%20strings%20which%20I%20have%20picked%20for%20this%20example.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22COUNTWORDS_STRINGSTOCOUNT.png%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246422i0B13137EB5911E19%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22COUNTWORDS_STRINGSTOCOUNT.png%22%20alt%3D%22COUNTWORDS_STRINGSTOCOUNT.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBreaking%20the%20problem%20down%2C%20what%20we%20are%20looking%20to%20do%20is%20find%20individual%20words%20in%20our%20sentence%2C%20but%2C%20what%20makes%20something%20a%20word%3F%20Well%2C%20naively%20speaking%2C%20we%20know%20that%20there%20is%20a%20word%20because%20there%20is%20a%20space%20before%20and%2For%20after%20a%20series%20of%20characters.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBecause%20of%20this%2C%20we%20can%20count%20the%20length%20of%20our%20string%20with%20and%20without%20spaces%20and%20then%20subtract%20the%20two.%20This%20should%20give%20us%20a%20valid%20count%20of%20how%20many%20%E2%80%9Cwords%E2%80%9D%20are%20in%20our%20text%20string.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20prove%20this%2C%20lets%20take%20a%20simple%20example%20of%20%E2%80%9CSally%20Jumps.%E2%80%9D.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20text%20string%20the%20counts%20are%20as%20follows%20and%20generally%20speaking%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Flen-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELEN%3C%2FA%3E%20function%20is%20good%20for%20this%3A%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2257px%22%3E%3CP%3ECalculation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2257px%22%3E%3CP%3EResult%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2257px%22%3E%3CP%3EFormula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CEM%3ECountOfLength%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E12%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3DLEN(%22Sally%20Jumps.%22)%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CEM%3E%3CSTRONG%3ECountOfLengthWithoutSpaces%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E11%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E%3DLEN(%22SallyJumps.%22)%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%3EWith%20those%20two%20values%20calculated%2C%20we%20can%20simply%20subtract%20the%20%3CEM%3ELengthWithoutSpaces%3C%2FEM%3E%20from%20the%20%3CEM%3ECountofLength%3C%2FEM%3E%20and%20that%20will%20give%20us%20the%20value%20%3CSTRONG%3E1%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20you%20might%20be%20thinking...%3C%2FP%3E%0A%3CP%3E%E2%80%9COK%2C%20we%20get%201%2C%20but%20that%20is%20wrong%E2%80%A6%20There%20are%20clearly%202%20words%20in%20that%20sentence!%E2%80%9D.%3C%2FP%3E%0A%3CP%3EYou%20would%20be%20correct%20and%20with%20this%20calculation%2C%20we%20will%20always%20be%20off%20by%201.%20This%20is%20a%20simple%20thing%20to%20fix%20and%20important%20to%20remember%20when%20authoring%20our%20final%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20summary%2C%20the%20formula%20we%20are%20going%20to%20build%20roughly%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTOFLENGTH(%26lt%3Btext%20string%26gt%3B)%20%E2%80%93%20COUNTOFLENGTH(%26lt%3Btext%20string%20with%20no%20spaces%26gt%3B)%20%2B%201%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20next%20thing%20to%20consider%20is%20how%20we%20can%20calculate%20a%20version%20of%20our%20text%20with%20no%20whitespaces%20and%20thankfully%20there%20is%20a%20formula%20for%20that!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20remove%20whitespaces%20from%20our%20text%20string%2C%20we%20can%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Fsubstitute-function-6434944e-a904-4336-a9b0-1e58df3bc332%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESUBSTITUTE%3C%2FA%3E%20function%20to%20search%20for%20any%20spaces%3A%20%22%20%22%20and%20replace%20them%20with%20nothing%3A%20%22%22%20.The%20formula%20would%20look%20like%20the%20following.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DSUBSTITUTE(%22Sally%20Jumps.%22%2C%20%22%20%22%2C%20%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20take%20that%20formula%20and%20replace%20it%20with%20the%20%22SallyJumps.%22%20portion%20of%20%3CEM%3ECountOfLengthWithoutSpaces%3C%2FEM%3E%20we%20can%20calculate%20this%20without%20modifying%20the%20original%20string!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DLEN(SUBSTITUTE(%22Sally%20Jumps.%22%2C%20%22%20%22%2C%20%22%22))%3C%2FPRE%3E%0A%3CP%3EWith%20both%20problems%20completed%2C%20we%20can%20now%20begin%20to%20construct%20the%20final%20version%20of%20our%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPutting%20together%20the%20intermediate%20calculations%20we%20created%2C%20(and%20assuming%20our%20text-string%20lives%20in%20A1)%20gives%20us%20the%20following%20solution%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DLEN(A1)%20%E2%80%93%20LEN(SUBSTITUTE(A1%2C%20%22%20%22%2C%20%22%22))%20%2B%201%3C%2FPRE%3E%0A%3CP%3EMoving%20along%2C%20all%20that%20is%20left%20to%20do%2C%20is%20encode%20this%20formula%20as%20a%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Flambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELAMBDA%3C%2FA%3E%20so%20we%20can%20use%20it%20later%20without%20having%20to%20think%20through%20this%20problem%20again!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%20our%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Flambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELAMBDA%3C%2FA%3E%20will%20only%20take%20one%20input%2C%20the%20text%20string%2C%20and%20will%20need%20to%20define%20two%20names%20within%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Flet-function-34842dd8-b92b-4d3f-b325-b8b8f9908999%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELET%3C%2FA%3E%20that%20map%20to%20our%20intermediate%20calculations.%20The%20formula%20looks%20like%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DLAMBDA(textString%2C%20LET(%3CBR%20%2F%3E%20%20%20%20%20countOfLength%2C%20LEN(textString)%2C%3CBR%20%2F%3E%20%20%20%20%20countOfLengthWithoutSpaces%2C%20LEN(SUBSTITUTE(textString%2C%20%22%20%22%2C%20%22%22))%2C%3CBR%20%2F%3E%20%20%20%20countOfLength%20-%20countOfLengthWithoutSpaces%20%2B%201%3CBR%20%2F%3E))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20our%20formula%20complete%2C%20some%20of%20you%20may%20have%20realized%20there%20is%20still%20one%20thing%20which%20could%20go%20wrong%20with%20our%20calculations%20and%20something%20we%20forgot%20to%20consider.%20What%20I%20am%20referring%20to%20is%20the%20case%20where%20there%20might%20be%20extra%20spaces%20included%20at%20the%20end%20of%20our%20text%20string.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20if%20I%20had%20the%20text%20string%2C%20%22Sally%20Jumps.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%22%2C%20our%20formula%20would%20return%20an%20improper%20count%20and%20as%20such%2C%20it%20is%20necessary%20for%20us%20to%20remove%20any%20leading%20or%20trailing%20whitespace.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20prove%20this%2C%20we%20would%20get%20the%20following%20counts%20if%20we%20used%20our%20current%20solution%20on%20the%20problematic%20text%20string.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2257px%22%3E%3CP%3ECalculation%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2257px%22%3E%3CP%3EResult%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2257px%22%3E%3CP%3EFormula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CEM%3ECountOfLength%3C%2FEM%3E%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E17%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3DLEN(%22Sally%20Jumps.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%22)%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CEM%3E%3CSTRONG%3ECountOfLengthWithoutSpaces%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CSTRONG%3E11%3C%2FSTRONG%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3E%3DLEN(SUBSTITUTE(%22SallyJumps.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%22%2C%20%22%20%22%2C%20%22%22))%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%3EThis%20would%20ultimately%20return%20the%20value%207!%20There%20are%20definitely%20less%20than%207%20words%20in%20that%20sentence%E2%80%A6%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThankfully%2C%20since%20we%20made%20us%20of%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Flet-function-34842dd8-b92b-4d3f-b325-b8b8f9908999%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELET%3C%2FA%3E%20and%20have%20abstracted%20many%20of%20our%20calculations%20away%2C%20it%20becomes%20trivial%20to%20modify%20our%20solution.%20Additionally%2C%20if%20we%20happened%20to%20be%20using%20this%20in%20many%20places%2C%20we%20don%E2%80%99t%20have%20to%20track%20them%20down%20since%2C%20with%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Flambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ELAMBDA%3C%2FA%3E%2C%20our%20formula%20is%20defined%20once%20and%20reused%20%3Asmiling_face_with_smiling_eyes%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMoving%20along%2C%20the%20final%20thing%20we%20need%20to%20do%20to%20fix%20this%2C%20is%20make%20use%20of%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Ftrim-function-410388fa-c5df-49c6-b16c-9e5630b479f9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETRIM%3C%2FA%3E%20function.%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Ftrim-function-410388fa-c5df-49c6-b16c-9e5630b479f9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETRIM%3C%2FA%3E%20removes%20leading%20and%20trailing%20spaces%20from%20a%20string.%20We%20can%20then%20make%20sure%20we%20call%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Foffice%2Ftrim-function-410388fa-c5df-49c6-b16c-9e5630b479f9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETRIM%3C%2FA%3E%20on%20%3CEM%3EtextString%3C%2FEM%3E%20before%20passing%20it%20into%20other%20calculations.%20There%20are%20a%20few%20ways%20you%20can%20re-author%20the%20above%20formula%2C%20but%20my%20solution%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DLAMBDA(_textString%2C%20LET(%3CBR%20%2F%3E%20%20%20%20%20textString%2C%20TRIM(_textString)%2C%3CBR%20%2F%3E%20%20%20%20%20countOfLength%2C%20LEN(textString)%2C%3CBR%20%2F%3E%20%20%20%20%20countOfLengthWithoutSpaces%2C%20LEN(SUBSTITUTE(textString%2C%20%22%20%22%2C%20%22%22))%2C%3CBR%20%2F%3E%20%20%20%20countOfLength%20-%20countOfLengthWithoutSpaces%20%2B%201%3CBR%20%2F%3E))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20that's%20it!%20You%20can%20now%20store%20this%20formula%20in%20the%20name%20manager%2C%20give%20it%20a%20name%20and%20call%20your%20newly%20created%20function!%20(In%20this%20case%20I%20chose%20to%20name%20it%20COUNTWORDS)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20last%20thing%2C%20I%20would%20like%20to%20point%20out%2C%20is%20how%20when%20I%20call%20the%20function%2C%20I%20make%20use%20of%20a%20range%20as%20the%20input%20into%20my%20function!%20I%20think%20this%20is%20a%20pretty%20cool%20use%20of%20dynamic%20arrays%20and%20makes%20it%20so%20I%20don%E2%80%99t%20even%20have%20to%20do%20a%20fill%20down%20and%20duplicate%20my%20formula%20per%20row!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22COUNTWORDS_GIF.gif%22%20style%3D%22width%3A%20851px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246426i9DF1C2EC62ACF2EB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22COUNTWORDS_GIF.gif%22%20alt%3D%22COUNTWORDS_GIF.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20hope%20you%20found%20this%20example%20useful%20and%20look%20forward%20to%20seeing%20what%20lambdas%20you%20have%20cooked%20up%20on%20your%20own.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUntil%20next%20time!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChris%20Gross%2C%3C%2FP%3E%0A%3CP%3EProgram%20Manager%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2061855%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-2062234%22%20slang%3D%22en-US%22%3ERe%3A%20LAMBDA%20Examples%3A%20Count%20words%20in%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2062234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584627%22%20target%3D%22_blank%22%3E%40chgross%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20lambda%20example%20that's%20great.%20As%20word%20counter%20-%20not%20sure.%20For%20example%2C%20in%20%22one%20-%20two%20-%20three%22%20it%20counts%205%20words.%20Microsoft%20Word%20-%20three%2C%20which%20meets%20my%20expectation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2068666%22%20slang%3D%22en-US%22%3ERe%3A%20LAMBDA%20Examples%3A%20Count%20words%20in%20a%20text%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2068666%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584627%22%20target%3D%22_blank%22%3E%40chgross%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20wondered%20about%20how%20to%20create%20LAMBDA%20functions%20that%20take%20a%20variable%20number%20of%20parameters.%20One%20possibility%20could%20be%20to%20define%20a%20function%20that%20takes%20a%20variable%20number%20of%20single%20arguments%20instead%20such%20as%2C%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EADD%3A%3DLAMBDA(a%2CLAMBDA(b%2CIF(TYPE(b)%3D16%2Ca%2CADD(a%2Bb))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20ADD%20function%20can%20take%20scalars%20or%20arrays%20as%20parameters%20and%20returns%20the%20sum%20of%20the%20entries%20across%20the%20single%20arguments%20provided%20a%20last%20dummy%20argument%20is%20inserted.%20For%20example%20the%20following%20formula%20returns%201%2B2%2B3%2B4%2B5%3D15%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DADD(1)(2)(3)(4)(5)(%5C)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20same%20sort%20of%20method%20could%20be%20used%20to%20extend%20any%20function%20from%20two%20inputs%20to%20n-inputs.%20For%20instance%20the%20%3CSTRONG%3Ea%2Bb%3C%2FSTRONG%3E%20operation%20could%20be%20modified%20to%20a%20function%20%3CSTRONG%3EUNION(a%2Cb)%3C%2FSTRONG%3E%20that%20stacks%20two%20arrays%20so%20that%20n%20arrays%20could%20be%20stacked%20together.%20It%20would%20be%20nice%20if%20there%20were%20a%20more%20intuitive%20way%20to%20do%20this%20however%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

This post is the second of a series where we will be sharing out examples of lambdas. This is intended to highlight lambdas we have cooked up that show the power of this new super-charged function. Additionally, you have the opportunity to engage with us on the lambdas you have built yourself and of course let us know how our own formulas could be improved. 

 

If you didn’t catch the announcement, be sure to check out the blog post highlighting the release of this new function: LAMBDA

 

In today’s example we will be creating a lambda which allows you to count how many words are in a text string.

 

I find myself making use of this formula all the time when I need to do text scrubbing or fact checking I have edited my data correctly. Hopefully, if you’re like me and find yourself making use of this formula from time to time, it gives you a more repeatable way to call it without having to write all the logic from scratch.

 

Count words in a text string

 

For this example, lets start with the problem we have at hand and would like to abstract as a custom function. Below you can see a series of text strings which I have picked for this example.

 

COUNTWORDS_STRINGSTOCOUNT.png

 

Breaking the problem down, what we are looking to do is find individual words in our sentence, but, what makes something a word? Well, naively speaking, we know that there is a word because there is a space before and/or after a series of characters.

 

Because of this, we can count the length of our string with and without spaces and then subtract the two. This should give us a valid count of how many “words” are in our text string.

 

To prove this, lets take a simple example of “Sally Jumps.”.

 

In this text string the counts are as follows and generally speaking the LEN function is good for this:

Calculation

 

Result

 

Formula

 

CountOfLength 12 =LEN("Sally Jumps.")
CountOfLengthWithoutSpaces 11

=LEN("SallyJumps.")

 

With those two values calculated, we can simply subtract the LengthWithoutSpaces from the CountofLength and that will give us the value 1.

 

Now you might be thinking...

“OK, we get 1, but that is wrong… There are clearly 2 words in that sentence!”.

You would be correct and with this calculation, we will always be off by 1. This is a simple thing to fix and important to remember when authoring our final formula.

 

In summary, the formula we are going to build roughly looks like this:

 

=COUNTOFLENGTH(<text string>) – COUNTOFLENGTH(<text string with no spaces>) + 1

 

The next thing to consider is how we can calculate a version of our text with no whitespaces and thankfully there is a formula for that!

 

To remove whitespaces from our text string, we can use the SUBSTITUTE function to search for any spaces: " " and replace them with nothing: "" .The formula would look like the following.

 

=SUBSTITUTE("Sally Jumps.", " ", "")

 

If we take that formula and replace it with the "SallyJumps." portion of CountOfLengthWithoutSpaces we can calculate this without modifying the original string!

 

=LEN(SUBSTITUTE("Sally Jumps.", " ", ""))

With both problems completed, we can now begin to construct the final version of our formula.

 

Putting together the intermediate calculations we created, (and assuming our text-string lives in A1) gives us the following solution:

 

=LEN(A1) – LEN(SUBSTITUTE(A1, " ", "")) + 1

Moving along, all that is left to do, is encode this formula as a LAMBDA so we can use it later without having to think through this problem again!

 

In this case, our LAMBDA will only take one input, the text string, and will need to define two names within the LET that map to our intermediate calculations. The formula looks like the following:

 

=LAMBDA(textString, LET(
countOfLength, LEN(textString),
countOfLengthWithoutSpaces, LEN(SUBSTITUTE(textString, " ", "")),
countOfLength - countOfLengthWithoutSpaces + 1
))

 

With our formula complete, some of you may have realized there is still one thing which could go wrong with our calculations and something we forgot to consider. What I am referring to is the case where there might be extra spaces included at the end of our text string.

 

For example, if I had the text string, "Sally Jumps.     ", our formula would return an improper count and as such, it is necessary for us to remove any leading or trailing whitespace.

 

To prove this, we would get the following counts if we used our current solution on the problematic text string.

 

Calculation

 

Result

 

Formula

 

CountOfLength 17 =LEN("Sally Jumps.    ")
CountOfLengthWithoutSpaces 11

=LEN(SUBSTITUTE("SallyJumps.    ", " ", ""))

 

This would ultimately return the value 7! There are definitely less than 7 words in that sentence…

 

Thankfully, since we made us of LET and have abstracted many of our calculations away, it becomes trivial to modify our solution. Additionally, if we happened to be using this in many places, we don’t have to track them down since, with LAMBDA, our formula is defined once and reused

 

Moving along, the final thing we need to do to fix this, is make use of the TRIM function. TRIM removes leading and trailing spaces from a string. We can then make sure we call TRIM on textString before passing it into other calculations. There are a few ways you can re-author the above formula, but my solution looks like this:

 

=LAMBDA(_textString, LET(
textString, TRIM(_textString),
countOfLength, LEN(textString),
countOfLengthWithoutSpaces, LEN(SUBSTITUTE(textString, " ", "")),
countOfLength - countOfLengthWithoutSpaces + 1
))

 

And that's it! You can now store this formula in the name manager, give it a name and call your newly created function! (In this case I chose to name it COUNTWORDS)

 

One last thing, I would like to point out, is how when I call the function, I make use of a range as the input into my function! I think this is a pretty cool use of dynamic arrays and makes it so I don’t even have to do a fill down and duplicate my formula per row!

 

COUNTWORDS_GIF.gif

 

We hope you found this example useful and look forward to seeing what lambdas you have cooked up on your own.

 

Until next time!

 

Chris Gross,

Program Manager Excel

2 Replies

@chgross 

As lambda example that's great. As word counter - not sure. For example, in "one - two - three" it counts 5 words. Microsoft Word - three, which meets my expectation.

@chgross 

I have wondered about how to create LAMBDA functions that take a variable number of parameters. One possibility could be to define a function that takes a variable number of single arguments instead such as,

ADD:=LAMBDA(a,LAMBDA(b,IF(TYPE(b)=16,a,ADD(a+b))))

This ADD function can take scalars or arrays as parameters and returns the sum of the entries across the single arguments provided a last dummy argument is inserted. For example the following formula returns 1+2+3+4+5=15:

=ADD(1)(2)(3)(4)(5)(\)

The same sort of method could be used to extend any function from two inputs to n-inputs. For instance the a+b operation could be modified to a function UNION(a,b) that stacks two arrays so that n arrays could be stacked together. It would be nice if there were a more intuitive way to do this however