How do I count the Characters in a cell

[Woozy]

What formula would I need to apply to count the characters in a cell and not
count spaces. e.g. Tom T Tucker = 10 not 12 as with the formula =LEN(A1).
Thanks in advance.

 

[Dave Peterson]

=len(substitute(a1," ",""))

You didn't ask, but if you wanted to count the spaces:
=len(a1)-len(substitute(a1," ",""))

 

[T. Valko]

Try this:

=LEN(SUBSTITUTE(A1," ",""))

 

[Ron Rosenfeld]

What formula would I need to apply to count the characters in a cell and not
count spaces. e.g. Tom T Tucker = 10 not 12 as with the formula =LEN(A1).
Thanks in advance.

=len(substitute(a1," ",""))

--ron

 

[Woozy]

Thanks thats just the job. I don't suppose I could change the formula to
ignore punctution marks, or is that asking a bit much.

 

[Shane Devenshire]

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(D14,A1))))

Where D14 contain , (comma) . (period), ? and !.

You can have as many punctuations in the range D1n 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(D13,A1))))

In this case notice that the . (period) is not included in the range D13.

If you want to get much fancier you probably should consider a VBA custom
function.