Lookup word and value
Hi Ron
All too easily done.<bg
I had been playing with exactly the same formula myself, and just
getting round to the substitute, when I noticed Bob's posting.
Rather than posting another solution to the OP, I chose to try to
correct Bob's.
If I hadn't already been playing with the problem, I probably wouldn't
have noticed either error.
--
Regards
Roger Govier
"Ron Rosenfeld" wrote in message
...
On Sun, 8 Jan 2006 17:24:55 -0000, "Roger Govier"
wrote:
Hi Ron
I don't think that would ignore the spaces, it would reduce the total
value by 32 for each space found wouldn't it?
I think you would need to use
SUBSTITUTE(A1," ","") for each occurrence of (A1) in your formula, or
add to the formula
+(LEN(A1)-LEN(SUBSTITUTE(A1," ","") ))*32
I pasted in the wrong formula <sheepish grin
=SUMPRODUCT(CODE(UPPER(MID(SUBSTITUTE(A1," ",""),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ","")))),1)))-64)
Thanks for picking that up.
--ron
|