In count words using LEN,TRIM,SUBSTITUTE how can make blank cell to give value blank in fill drag process..

[pankajgrover]

here is screenshot attached. in Q5 i am trying to count words of C5, but when i drag fill it counts also blank value and fill 1 value . how to do put value as blank in Q5.
I am using this =LEN(TRIM(C5))-LEN(SUBSTITUTE(C5," ",""))+1 but it gives blank column value as 1 instead of blank. Thanks.

 

[Automatrix]

Does this work for you?

=IF(C5="","",LEN(TRIM(C5))-LEN(SUBSTITUTE(C5," ",""))+1)

 

[Fluff]

Another option

=IFERROR(ROWS(TEXTSPLIT(C5,," ")),"")

 

[pankajgrover]

@Automatrix and @Fluff Wow both options are working fantastic. Great and quick support. Thanks to all support members

 

[Fluff]

Glad we could help & thanks for the feedback.