Excel Magic Trick #221: LEN function Counts Spaces

See how to use the LEN function to count charters including spaces. Then see how to use the LEN, SUBSTITUTE, and TRIM function to count characters but not count unwanted spaces.

Category:

Science & Technology

Tags:

• Excel
• LEN
• SUBSTITUTE
• TRIM
• function
• count
• characters
• spaces
• not
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[nombrejill]

THANK YOU. My life had been turned into hell by a client who sends me pages and pages of copy in Excel and wants me to modify it, keeping it all under certain character limits. I have never used Excel before so finding this is a real godsend. You have reduced my anger level by at least 50%.

[ExcelIsFun]

@nombrejill , I am glad that the video helps!

[f4st3]

What if I wanted to Find the length of one word in a cell with two words?

so lets say

John Smith, How do I use the function to find the number of characters in Smith

[ExcelIsFun]

You Tube won't let me post the characters needed to answer your questions so I sent you an e-mail.

[khorse69]

how do i use this to delete the last 2 words in a cell??

[ExcelIsFun]

Did you post to the Mr Excel Message Board? I saw a post that asked the same question.

I posted there and got a few answers such as:

=LEFT(A8,FIND("qq",SUBSTITUTE(­A8," ","qq",LEN(A8)-LEN(SUBSTITUTE(­A8," ",""))-1))-1)

=SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200)),"")

I'll make a video soon because how these two solutions work is very interesting - two totally different approaches.

[khorse69]

hi yea i did post on the forum, thanks alot for your help..keep up the great work!!

[ExcelIsFun]

We have to find a pattern to exploit. Are there single spaces between each word?