How do I count the Characters in a cell

  • Thread starter Thread starter Woozy
  • Start date Start date
W

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.
 
=len(substitute(a1," ",""))

You didn't ask, but if you wanted to count the spaces:
=len(a1)-len(substitute(a1," ",""))
 
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.
Click to expand...

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

--ron
 
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.
 
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:D4,A1))))

Where D1:D4 contain , (comma) . (period), ? and !.

You can have as many punctuations in the range D1:Dn 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:D3,A1))))

In this case notice that the . (period) is not included in the range D1:D3.

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