how to count words (English and Chinese together) in excel ?

[sunnyfunghy]

Example:

你當時見到嘅景象係點樣，Shadow？

Shadow->1 word

But my solution: =len(substitute(A1,"，",""))-1

But it also count "shadow" as six words, not 1 word, How to solve? Please help me please

 

[ama_trig]

I don't know it this could be done directly via cell, but with VBA it is definitely possible.
Process
A)Break the cell into string array, containing your all word list.
like this:
wordSplitedarray(0) = "你當時見到嘅景象係點樣"
wordSplitedarray(1) = "shadow"


B)
[ excel work out] run a code function, to see if first starting character is returning as unicode,
will return 63 for unicode

testing with cell:
cell = code("你當時見到嘅景象係點樣") = 63!

[ VBA work out ]Run following into for each loop
check to see first character, is in range of English or not

C) this means it should not be counted as a english word
the rest is clear
Follow this, might give you ideas.
**broken link removed**