Hi,
Its not too much to ask -
Try:
=LEN(SUBSTITUTE(A1,"
",""))-SUMPRODUCT(--(ISNUMBER(FIND({",";".";"?";"!"},A1))))
or its equivalent with the puctuations characters entered in cells:
=LEN(SUBSTITUTE(A1," ",""))-SUMPRODUCT(--(ISNUMBER(FIND(D1
4,A1))))
Where D1
4 contain , (comma) . (period), ? and !.
You can have as many punctuations in the range D1
n but no punctuation can
appear more than once in the cell being tested.
If you are looking at names, as your initial post suggested, the one
punctuation that might appear more than once is the period, to handle that
use a modified version of the first formula:
=LEN(SUBSTITUTE(SUBSTITUTE(A1,"
",""),".",""))-SUMPRODUCT(--(ISNUMBER(FIND(D1
3,A1))))
In this case notice that the . (period) is not included in the range D1
3.
If you want to get much fancier you probably should consider a VBA custom
function.