Counting No of Characters Excluding Spaces

I wish to count the number of characters in a particular cell, excluding any spaces, and display the number of characters in another cell. Spaces may be at the start, within the characters or at the end.

I'm sure this is quite easy, but I just can't seem to make to work.

Can someone please help?

Thanks.

Try this

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

Originally Posted by Phuocam

Try this

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

Perfect. Thanks heaps.

Originally Posted by Rick Rothstein

While I would use what Phuocam posted, I thought I would have some fun with this question (if you have xl365 that is)...

=SUM(LEN(TEXTSPLIT(A1," ")))

Works just as well. Any advantage of one over the other?

Originally Posted by EdWoods

Works just as well. Any advantage of one over the other?

Phuocam's formula is simpler (two function calls versus the three in mine) and I am thinking SUBSTITUTE may be less "demanding" than TEXTSPLIT. So for your question, I think Phuocam''s formula is better (which is why I said I would use it). However, if you changed your question to number of characters excluding spaces, commas and periods, then my formula could be easily modified to handle it...

=SUM(LEN(TEXTSPLIT(A1,{" ",",","."})))

Originally Posted by Rick Rothstein

Phuocam's formula is simpler (two function calls versus the three in mine) and I am thinking SUBSTITUTE may be less "demanding" than TEXTSPLIT. So for your question, I think Phuocam''s formula is better (which is why I said I would use it). However, if you changed your question to number of characters excluding spaces, commas and periods, then my formula could be easily modified to handle it...

=SUM(LEN(TEXTSPLIT(A1,{" ",",","."})))

Thanks. Very handy to put in my "Solutions" spreadsheet.

Originally Posted by EdWoods

Thanks. Very handy to put in my "Solutions" spreadsheet.

You are quite welcome. Question - your profile says you are using xl2019, but it is my understanding that TEXTSPLIT is not in that version of Excel... am I wrong?

Originally Posted by Rick Rothstein

You are quite welcome. Question - your profile says you are using xl2019, but it is my understanding that TEXTSPLIT is not in that version of Excel... am I wrong?

My profile is wrong. I'm on my work computer, which is 365.

Just on my previous question, suppose I ONLY wanted to allow input of digits 0 - 9 ignoring any other key pressed. How would I go about that?