How do you stop LEN counting spaces within the text?

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
 
C

Chip Pearson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do you remove trailing spaces withour Trim function? 8
Preserve trailing spaces in a textbox 8
how do I remove empty spaces trailing a text string? 2
Len and InStr problem manipulating strings 3
How do I eliminate spaces/characters from the beginning of a cell 1
SQL -- How do I remove trailing spaces 6
How do I count cells with text but ignore cells with spaces? 2
Extracting keywords from a string 8

Top