# Count No of Text in a cells

##### New Member
Hi,

I am new to Power Bi and learning DAX.
Well, i have scenario below:

Table1
Column A Results
BP,SUGAR,DIABETES,HYPERTENSION 4
BP,SUGAR,DIABETES 3

Here, i want to count no of words(BP,SUGAR,DIABETES) are there in a cell.

Ravi

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

##### New Member
Hi,

I am new to Power Bi and learning DAX.
Well, i have scenario below:

Table1
Column A Results
BP,SUGAR,DIABETES,HYPERTENSION | 4
BP,SUGAR,DIABETES | 3

Here, i want to count no of words(BP,SUGAR,DIABETES) are there in a cell.

Ravi

#### JustynaMK

##### Well-known Member

You can try an old Excel trick for this DAX formula:
Rich (BB code):
``CountWords = LEN(Table1[Column A]) - LEN(SUBSTITUTE(Table1[Column A], ",", "")) + 1``
The main part is counting how many times a comma (",") appears in a word, then we need to add +1 at the end of the formula to return the actual number of words.

#### Attachments

• 9.9 KB Views: 1

#### sandy666

##### Well-known Member
with Power Query and without duplicating worksheet formula
`SplitCount = Table.AddColumn(Source, "SCount", each List.Count(Text.Split([raw],",")))`
 raw SCount BP,SUGAR,DIABETES,HYPERTENSION 4 BP,SUGAR,DIABETES 3

simply add Custom Column, name it (here: SCount) and use: `List.Count(Text.Split([raw],","))` inside

Last edited:

##### New Member
Thank you very much.... JustynaMk & Sandy666

I have used above code and implemented in my project ...

You are welcome

1,095,170
Messages
5,442,799
Members
405,197
Latest member
queryashish

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...