I'm trying to find a formula that will count just words (text) in a range that also includes numbers. Lets say there are 4 words and 5 numbers mixed up in range E1:E9 =Counta comes up with 9 whilst Count returns 5. Excel Help suggest an array formula =SUM(IF(LEN(TRIM(E1:E9))=0,0,LEN(TRIM(E1:E9))-LEN(SUBSTITUTE(E1:E9," ",""))+1)) where E1:E9 contains text and numbers but it comes up with the answer of 9. I suppose I could use COUNT and COUNTA then take one from another but wondered if there was a way though the suggested formula is a real mouthful. Thanks
I thought that would probably be the best way. Can't imagine my ECDL students getting to grips with the array formula (let alone me). Just wondered though if there was a function hidden away somewhere. Thanks for the advice.