Excel Magic Trick #221: LEN function Counts Spaces

[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]

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

[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!!