letter count in a cell minus punctuation marks?
Steve
or punctuations marks.
Thanks
Steve
Jim May
going to have to be done using a Looping procedure (code) and only counting
those characters which are tested that are only BETWEEN CHAR(65) and
CHAR(90) (for Capital, A-Z) and BETWEEN CHAR(97) and CHAR(122) (for
lowercase, a-z). That's all I can contribute. Maybe you can go from
there...
"Steve" <g...@nb.net> wrote in message
news:9Fte9.185204$kp.7...@rwcrnsc52.ops.asp.att.net...
Aladin Akyurek
=SUM((SETV(CODE(MID(UPPER(A1),INTVECTOR(LEN(A1),1,1,1),1)))>=65)*(GETV()<=90
))
Requires the morefunc add-in from: http://longre.free.fr/english/index.html
Aladin
"Steve" <g...@nb.net> wrote in message
news:9Fte9.185204$kp.7...@rwcrnsc52.ops.asp.att.net...
Harlan Grove
>Array-enter:
>
>=SUM((SETV(CODE(MID(UPPER(A1),
>INTVECTOR(LEN(A1),1,1,1),1)))>=65)*(GETV()<=90))
>
>Requires the morefunc add-in from: http://longre.free.fr/english/index.html
Very clever. However, it can be done (albeit with a longer formula) without
MOREFUNC.XLL and without an array formula.
=SUMPRODUCT((ABS(CODE(MID(UPPER(A1),
ROW(INDIRECT("1:"&LEN(A1))),1))-77.5)<13)+0)
Jim May
your solution, just not-so when they are all combined together. Would you
mind "verbalizing" through this "big-guy" for me? It would be most helpful,
to help me get on my feet.
=SUMPRODUCT((ABS(CODE(MID(UPPER(A1),
ROW(INDIRECT("1:"&LEN(A1))),1))-77.5)<13)+0)
Tks in Advance..
JMay
"Harlan Grove" <hrl...@aol.com> wrote in message
news:uZ5kbgwVCHA.2416@tkmsftngp09...
Steve
Would it be too much to ask if this could also be done to count spaces " "
?
Steve
"Harlan Grove" <hrl...@aol.com> wrote in message
news:uZ5kbgwVCHA.2416@tkmsftngp09...
Roger Govier
I was also amazed by this very elegant solution, and I am sure that I cannot
explain it as well as Harlan, but as you have had no response for 6 hours,
I will give it a shot at explaining.
Forcing the UPPER value for any characters in cell A1, means that the range
of ASCII codes for the characters we are interested in fall between 65 (A)
and 90 (Z). The average of these is 77.5 which is then used within the
formula.
By taking 77.5 away from the code of each character within the cell, one may
end up with positive or negative numbers.
If the absolute of these numbers is less than 13, then the code must be
within the range 65 to 90, and therefore one of the values you are looking
for.
The ROW(INDIRECT("1":&LEN(A1)))
part, is telling the formula to take each character in turn from 1 to
whatever is the total length of the string of characters within cell A1, and
the MID(UPPER(A1) .... ,1) is saying take 1 character.
So Starting from MID(A1,1,1) through to MID(A1,x,1) where x is the length of
the string, the CODE for UPPER case each character in turn is having 77.5
taken away from it.
The ABS of this value is being compared to see if it is less than 13, which
will return TRUE or FALSE.
The +0 at the end, is then coercing these values into 1 for TRUE and 0 for
FALSE.
SUMPRODUCT() then adds all of these 1's and 0's to give the required
solution.
Harlan, I hope that you don't mind me "butting in" and I hope that I have
got the explanation correct.
Thank you for this (and many other) of your solutions, which I usually do
work through and try to understand.
--
Regards,
Roger Govier
Technology 4 U
"Jim May" <jm...@cox.net> wrote in message
news:icIe9.107009$082.4...@news1.east.cox.net...
Tom Ogilvy
+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
to the end of Harlan's formula.
Regards,
Tom Ogilvy
Steve <g...@nb.net> wrote in message news:ZuIe9.243933$aA.41349@sccrnsc02...
Tom Ogilvy
so their ascii char numbers are 65 - 90
Subtract 75.5 so their value now spans the range
-12.5 to +12.5
any other character will be less than 13 ( 64 - 7.5 = -13.5) or greater
than 13 (91-77.5 = 13.5)
Test the absolute value being less than 13.
Regards,
Tom Ogilvy
Jim May <jm...@cox.net> wrote in message
news:icIe9.107009$082.4...@news1.east.cox.net...
Steve
But I just did this:
))-77.5)<13
to
))-32)=0
So it counts the number of spaces (code=32)
Steve
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:OtKJEf0VCHA.1340@tkmsftngp10...
Tom Ogilvy
is probably more efficient than an array formula, but suit yourself.
Regards,
Tom Ogilvy
Steve <g...@nb.net> wrote in message news:H2Ke9.404397$UU1.62408@sccrnsc03...
Jim May
JMay
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:eJI8Af0VCHA.1340@tkmsftngp10...
Jim May
explaining this guy.
We should put Harlan onto writing formulas that solve life-long mysteries
such as:
1) Which came first? "the chicken or the egg".
or
2) What is the Cure for Cancer?
This made my day...
Thanks again Roger,,
JMay
Roanoke, VA
USA
"Roger Govier" <ro...@technology4u.co.uk> wrote in message
news:#m3RmW0VCHA.1460@tkmsftngp08...
Tom Ogilvy
In case you haven't stumbled on this trick yet,
Select a cell, go to the formula bar, highlight any complete subset of a
formula, such as
ROW(INDIRECT("1":&LEN(A1)))
Then Hit F9 and you will get this evaluated and show you want that part of
the formula is doing.
Hit the escape key to return that portion of the formula.
Then you can break these down yourself.
Regards,
Tom Ogilvy
Steve
So I tried putting an if statement before it to check if the cell is empty,
but
the formula still wants to calculate past the if condition and therefore
gives me the above error message.
I even tried using ISERROR statements but it did not work.
I just wanted a blank cell to display if there was no value for the cell
evaluated.
Can I change the font or format of the cell from within the if statement?
So if error, then hide or font=white, etc..
Steve
"Harlan Grove" <hrl...@aol.com> wrote in message
news:uZ5kbgwVCHA.2416@tkmsftngp09...
Tom Ogilvy
1))-77.5)<13)+0))
Array entered worked fine for me.
Regards,
Tom Ogilvy
Steve <g...@nb.net> wrote in message news:gtOe9.247324$aA.41623@sccrnsc02...
Jim May
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:OhVHFF3VCHA.2540@tkmsftngp09...
Tom Ogilvy
If it says too many cells to evaluate, just modify the whole formula to look
at a smaller range, then try it again.
Regards,
Tom Ogilvy
Jim May <jm...@cox.net> wrote in message
news:76Se9.108536$082.4...@news1.east.cox.net...
Jim May
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:OzANWK6VCHA.1640@tkmsftngp11...
Leo Heuser
news:uZ5kbgwVCHA.2416@tkmsftngp09...
>
> Very clever. However, it can be done (albeit with a longer formula)
without
> MOREFUNC.XLL and without an array formula.
>
> =SUMPRODUCT((ABS(CODE(MID(UPPER(A1),
> ROW(INDIRECT("1:"&LEN(A1))),1))-77.5)<13)+0)
>
Even if the formula is not array-entered, I take it, that it's
still an array formula, since the arguments of the function
are arrays??
However, I have difficulties finding out, when one can enter
SUMPRODUCT() with <Enter>, and when it's necessary to
array-enter it. Arrays created from an IF-condition and the using
of TRANSPOSE() seems to call for array-entering, but I can't
seem to pinpoint a pattern. Maybe you can shed some light
on this, Harlan?
--
Best regards
Leo Heuser
Stephen Dunn
I'm sure I'm not going to give the best explanation of this, but here's
something to think about whilst awaiting a better one.
Some functions (SUMPRODUCT is a good example) are array functions, but that
does not necessarily make them array formulae.
In general, functions that accept arrays as arguments, do not necessarily
give arrays as their result, in which case they can be entered as normal.
If they do give an array result(e.g. the FREQUENCY function), they must be
entered (over several cells) as an array formulae.
However, _formulae_ that deal with arrays (that do not involve array
functions) must be entered as array formulae regardless of whether they
return a single result or an array result.
Example of array function that returns single result (no need for array
entering):
=SUMPRODUCT(list*(list>5)*(list<10))
equivalent array formulae (needs to be entered as array formulae):
{=SUM(IF(list>5,IF(list<10,list)))}
the difference being that SUMPRODUCT knows it has to do something with each
item of an array in turn, but SUM would normally just operate on a range,
and not concern itself with the different dimensions of the array.
Steve D.
having read this again, I realise that I'm a hell of a lot better at
understanding these things than I am at explaining them. Sorry, I hope it
makes sense.
"Leo Heuser" <leo.h...@get2net.dk> wrote in message
news:#L8fwgAWCHA.2444@tkmsftngp09...
Harlan Grove
...
>Even if the formula is not array-entered, I take it, that it's
>still an array formula, since the arguments of the function
>are arrays??
Matter of semantics. I restrict myself to calling 'array formulas' only
those formulas that must be entered with [Ctrl]+[Shift]+[Enter] to obtain
the correct (or at least intended) result. Anything that can be entered with
[Enter] alone and still get the desired result is, thus, not an array
formula. SUMPRODUCT is an array *function* than returns a scalar. As long as
it's arguments contain nothing but operators and functions that don't return
error values if the formula as a whole isn't array-entered, then formulas
using SUMPRODUCT don't have to be array-entered. However, is functions
nested inside SUMPRODUCT must be array-entered in order to give non-error
results (such as most IF functions), then those formulas using SUMPRODUCT
*must* be array-entered. If you want a simple rule, "when in doubt,
array-enter."
Leo Heuser
"Harlan Grove" <Hrl...@aol.com> skrev i en meddelelse
news:hP6f9.5476$1C2.5...@bgtnsc04-news.ops.worldnet.att.net...
Best regards
Leo Heuser
Leo Heuser
news:ukDbcJBWCHA.1836@tkmsftngp08...
Hi Steve
It does make sense :-)
Thanks for the explanation. Much appreciated.
Best regards
Leo Heuser