Count How Many Words are in a Cell in Excel

Подписаться 106 тыс.

Просмотров 103 тыс.

50% 1

Count how many words are in a cell in Excel. Using a combination of text functions we can count the number of words in a cell. Excel does not currently provide a function to do this.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

Хобби

Опубликовано:

18 дек 2012

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 93

@music4kittens 9 лет назад

Very clear. Thank you!

@mohideenthassim7180 7 лет назад

Great tutorial Alan, many thanks.

@annoruka 11 лет назад

This was very helpful. Thank you!!!

@rufusboo1 10 лет назад

Brilliant "how to" video, thanks for sharing this. I like that you are a real person and not a totally smooth operator…. I'll be back for more "how too's"

@avdiva76 10 лет назад

Thanks! This helped!

@wayneedmondson1065 5 лет назад

Hi Alan.. just viewed this video.. clever! Thumbs up!

@9071040 10 лет назад

Excellent How to Video

@bigworldzooltd7956 2 года назад

Thanks mate! Been trying to do this very same thing for ages, nice work.

@Computergaga 2 года назад

No problem 👍

@cmaldavis 3 года назад

Thank you so much!! I could not get this to work but you made it very easy.

@Computergaga 3 года назад

Thank you, Amy. Great to hear 👍

@spmqchem 3 года назад

Thanks so much. I want to know how to compute the position of a specific word on a string?

@karenbroughtonn.z1629 4 года назад

Thank you so much this is exactly what I have been looking for.

@Computergaga 4 года назад

You're welcome, Karen. Great to hear.

@AquariumHobbist Год назад

Works a charm, thank you!

@Computergaga Год назад

Great! You're welcome!

@chinthakadiwelwatta27 7 лет назад

Thank you very much..Very useful

@Computergaga 7 лет назад

Your welcome Chinthaka, thanks.

@firstvideo6339 5 лет назад

best tutorial... old, but still works great.... on mac too. thanks a lot!!!

@Computergaga 5 лет назад

No problem. Happy to help.

@DhanashriBhate 4 года назад

Thanks, this is very helpful.

@Computergaga 4 года назад

Great! You're welcome.

@aankalaanka7001 2 года назад

Thank you so much, This is exactly what i was looking for..

@Computergaga 2 года назад

Glad I could help, Aanka.

@tigerallied 11 лет назад

Thank you very much for the formula. Thought don't think you need the TRIM function since you're substiting every space with a nospace (in the second part of the formula). Cheers!

@Nevir202 Год назад

You absolutely do. Otherwise "Hello " is 10 words

@rajakaz4075 3 года назад

Great! You saved our life! :D

@Computergaga 3 года назад

Glad I could help!

@lumongga45 2 года назад

Thank you so much. This is really help me a great lot.

@Computergaga 2 года назад

Fantastic! Great to hear that it helped, Lena.

@rakich8106 6 лет назад

Thank you very much

@Computergaga 6 лет назад

You're welcome.

@renatoguntendorfer6544 3 года назад

Thanks!!!!!

@Computergaga 3 года назад

You're very welcome, Renato.

@abuzarazizi3965 3 года назад

Thanks sir, Its Work.

@Computergaga 3 года назад

Great! No worries.

@drheba7543 2 года назад

oooh u saved my life .. thank u so much

@Computergaga 2 года назад

You're welcome!

@Drkalaamarab 4 года назад

👏👏👏👏👏👏brilliant share

@Computergaga 4 года назад

Thank you Praveen.

@John-jt3dn 3 года назад

Great video thanks.. I have spaces and special characters in my column too but this formula counts them as 1 word as well. Is there a way to fix this?

@Computergaga 2 года назад

You're welcome. I wouldn't expect spaces to be counted. As for special characters, sounds like you need to remove them like the spaces in the video. You can use CLEAN or may even need SUBSTITUTE for this. It depends on the characters and your region.

@majidsiddique8227 5 лет назад

Great Alan

@Computergaga 5 лет назад

Thank you Majid.

@workumulu0913 Месяц назад

Thank you Dear For Your Vedeo

@Computergaga Месяц назад

You are so welcome!

@vpjoshiassociates8431 5 лет назад

very helpful

@Computergaga 5 лет назад

Thank you.

@briankpaine Год назад

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 I'm just gonna leave this right here for easy access :) I really appreciate the video though.

@danieltrujillo4326 Год назад

This was very helpful. I am have trouble with getting the count of words in different cells put into one cell. Not sure if that makes sense.

@Computergaga Год назад

Great! Not entirely 😂 but sound like functions such as TEXTJOIN and CONCAT are helpful.

@abdullahagrobd6862 3 года назад

Thanks bro

@Computergaga 3 года назад

No problem

@geedoctor 3 года назад

THANK YOU -

@Computergaga 3 года назад

You're welcome.

@haislrekajsolre7876 Год назад

good one

@Computergaga Год назад

Thank you very much, Haislre.

@haislrekajsolre7876 Год назад

very nice sir

@Computergaga Год назад

Thank you.

@honeyboy7688 3 года назад

thank u soo much

@Computergaga 3 года назад

Most welcome 😊

@honeyboy7688 3 года назад

@@Computergaga My whole problem which m facing during audit u just gave me what exactly i want and m really Thank full to u sir 🙏🙏🙏

@FiStasH 2 года назад

It doesn’t work for me. In substitute, I get the first comma and the first double inverted commas highlighted. And then in the bar bellow the formula I have “text” in bold. Do you happen to know what I did wrong?

@Computergaga 2 года назад

I'm not sure exactly on what you see. It could be that you need a semi colon ; instead of a comma.

@noohc1 2 месяца назад

tks from 2024!

@Computergaga 2 месяца назад

My pleasure!

@enteradj 4 года назад

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),"",""))

@iscribeonline 3 года назад

You're awesome. I was hoping the RU-vidr would have included this formula in the description. Glad you did.

@iscribeonline 3 года назад

Just noticed that you missed out a few things. This formula is more accurate: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

@nolE69_420 4 года назад

cool but can you determine how many cells have more than (for example) 6 characters

@Computergaga 4 года назад

Sure. The LEN function returns the number of characters in a cell. So use this in a column and then use COUNTIF on it to count those with more than 6 characters. If you have the new dynamic arrays you could put this all in one formula. Or use it in SUMPRODUCT. However having the LEN helper column may be preferable.

@nolE69_420 4 года назад

@@Computergaga Whoa,THANKS MAN!!! I've been struggling with that one for a while now.

@richardchelstowski2191 6 лет назад

The formula is not perfect. What happens if the text contains any single character e.g. a hyphen, $ sign, or / ? If possible I would VBA. I would replace special characters with spaces or null, then use SPLIT function and finally check the size of the array.

@Computergaga 6 лет назад

Thank you for your comment Richard. Yes this is just a basic formula to return this result. We could improve the formula to replace hyphens also, but VBA is a great alternative. Thank you for your suggestion.

@dwn011974 5 лет назад

when i type this in =LEN(A2)-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 i get an error what am i doing wrong

@Computergaga 5 лет назад

Depends on the error. If it is #VALUE! it may be caused by some inconsistency with formatting.

@dwn011974 5 лет назад

@@Computergaga I believe that I pressed dash not minus I think that's what ive done its working fine now thank you for your swift reply

@Computergaga 5 лет назад

You're welcome. Good work Dawn.

@mohamedrinfas870 3 года назад

How to count many row single letter

@Computergaga 3 года назад

Check out this video Mohamed - ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-CfgJNjxWRrw.html If you want multiples rows then select the range instead of a single cell and wrap a SUM around it. If you use a version before 365 then you will need to run it as an array formula.

@talhabinhasan8966 6 месяцев назад

these are the words... you havent tell what about if numbers were there not words please help

@geedoctor 3 года назад

=LEN(TRIM(put the box location here))-LEN(SUBSTITUTE(TRIM(put the box location here)," ",""))+1

@YKocak-sq8db 3 года назад

=LEN(TRIM(A1))

@Computergaga 3 года назад

@hamoenrique1973 3 года назад

In Microsoft Excel we can write as many words in each cell

@Computergaga 3 года назад

No, there is a limit.

@RaviKumar-xn6xr 2 года назад

Sir worda kaha count hue ye to space count kare hai,wrong information hai

@markdevilliers4324 4 года назад

I TYPE IT IN WORD FOR WORD, DOES NOT WORK

@Computergaga 4 года назад

There must be a mistake somewhere Mark. Check through the formula.

@dwdapo8030 3 года назад

HOW TO COUNT NUMBER OF DIGITS IN A CELL

@robertocarlosconcepcion3823 2 года назад

I was getting something different so i try to updated it to what works for me, here is my version, I hope it can help someone: IF(C2="","",1+(LEN(TRIM(C2))-LEN(SUBSTITUTE(TRIM(C2)," ",""))))