How do you stop LEN counting spaces within the text?

  • Thread starter Thread starter Noel S Pamfree
  • Start date Start date
N

Noel S Pamfree

Hi,

I have lists of thousands of villages and need to know how many letters
there are in each. The problem is that some have two words like 'Barnham
Broom' and the LEN function returns the length which includes the space.

I have made some attempts to solve the problem but TRIM only trims trailing
spaces. FIND can search for a space and when I try using an IF statement it
gives an error message each time there is no space!

Any ideas?

Noel
 
Noel,

Use the SUBSTITUTE function to change the spaces to empty
strings, and then use LEN on that result. E.g.,

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



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top