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," ",""))


Woozy wrote:

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

Try this:

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

--
Biff
Microsoft Excel MVP


"Woozy" wrote in message
...
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.

On Wed, 7 Jan 2009 19:26:17 -0800, Woozy
wrote:

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

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.

"Dave Peterson" wrote:

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

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


Woozy wrote:

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

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Woozy" wrote:

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.

"Dave Peterson" wrote:

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

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


Woozy wrote:

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