Today we are going to discuss about numerous methods in Excel functions and M-Code to get count of words in a cell. Lot to learn, Let's dive in.
Method-1:
Works in all versions, need to drag the formula down.
Formula in C3 cell is,
=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1
Concept is simple,
Pick overall length of Text,
Then pick length after excluding spaces.
Subtract length of text without space from overall length. This will give us the number of spaces. Number of spaces will always be one less than number of words, so if we add one, we will get number of words.
Method-2:
=COUNTA(FILTERXML("<A><B>"&SUBSTITUTE(B3," ","</B><B>")&"</B></A>","//B"))
In XML argument,
"<A><B>Sachin</B><B>Ramesh</B><B>Tendulkar</B></A>"
In XPath argument,
"//B"
This will extract all nodes as shown below,
{"Sachin";"Ramesh";"Tendulkar"}
COUNTA will deliver us the count of non-blank cells, which is 3.
Method-3:
Office 365 version - Single cell formula,
=LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7," ",""))+1
This is same as Method-1 but will spill the result. So, no need to drag the formula down.
Method-4:
Office 365 version, Single cell formula,
=SCAN(0,B3:B7,LAMBDA(a,v,COUNTA(TEXTSPLIT(TRIM(v)," "))))
This is the LAMBDA helper function, which will do the custom function in each iteration.
Our requirement is to get count of words,
So, our starting position is number zero,
array is B3:B7 range,
In LAMBDA function,
a -> State and v -> Current,
In first iteration,
a -> 0, but we are not going to use this,
v -> B3 cell value -> 'Sachin Ramesh Tendulkar',
TEXTSPLIT function will split B3 cell value with space as Column delimiter,
{"Sachin","Ramesh","Tendulkar"}
COUNTA counts the non-blank cell which is 3.
In next step, v will carry b4 cell value.
These steps will be carried out for each cell in the range B3:B7.
We can use below M-code to extract count of words.
Method-5:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn( Source, "Count", (f) =>try List.Count( Text.Split( Text.Trim(f[Names]), " ")) otherwise 0 )
in
Result
Table.AddColumn helps us to add column namely 'Count' in 'Source' table.
The operation, what we are going to perform in new column is that,
In each row, extract data from [Names] column, then TRIM it to remove extra spaces before and after.
Text.Split function helps us to split this text by using space as delimiter.
It will deliver us the words in list and List.Count helps us to get the count of words.
Method-6:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn( Source, "Count", (f) => try List.Count( List.Select( Text.ToList(
Text.Trim(f[Names])), each _ = " ")) + 1 otherwise 0 )
in
Result
Comments