Count multiple occurences of a certain string in one cell (row)

[Karthelos]

Hello,

Newbie here.

I have data (inherited an excel file) where per row (in a certain cell) I can have several occurances of a special abbrevation (i.e. ABC1).

I need to count every single occurance of that certain abbrevation. For example:

Lorem ipsum dolor sit amet, consectetur adipiscing elit ABC1, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco ABC1 laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum ABC1.

This should get me a count of 3, but the COUNT variants return 1 of course, as per their description.

Is there any way to get the correct count of the abbrevation? I do not have the time nor skills or knowledge to make  a power app of the excel, which would make the entries right so I could count them correct from the start.

If there is already a solution in this forum, I could not find it due to english being my second language and I could not find the right keywords for the search, sorry for that.

[tamerj1 · Accepted solution]

Hi @Karthelos 

for a measure you can use

Count =
SUMX (
'Table',
VAR String = 'Table'[Column]
VAR Items =
SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( String, ",", "" ), ".", "" ),  " ", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 =
FILTER ( T2, [@Item] = "ABC1" )
RETURN
COUNTROWS ( T3 )
)

for a calculated column you can use

Count =
VAR String = 'Table'[Column]
VAR Items =
SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( String, ",", "" ), ".", "" ),  " ", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 =
FILTER ( T2, [@Item] = "ABC1" )
RETURN
COUNTROWS ( T3 )

[Karthelos]

Thank you for your answer,

I use a measure.

SUMX (
'Table',
VAR String = 'Table'[Column]
VAR Items =
SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( String, ",", "" ), ".", "" ),  " ", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 =
GENERATESERIES ( 1, Length, 1 )
VAR T2 =
SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR T3 =
FILTER ( T2, [@Item] = "ABC1" )
RETURN
COUNTROWS ( T3 )
)

I changed the code in red to the fitting values and get more counts of the string, but by far not as much as there are. It should be 78, but the formula counts just 10.

The text in the cell is something like:

S. 3, FE1, nicht eingetragen

S.20 FE1 nicht angekreuzt

S 34,FE1, 2x N/A

...and other abbrevations plus text, for each I use a different measure for. I do not want to give too much information.

Plus I filter by Weeks and epartments, so there is a list with several rows where FE1 appears. But the measure did just fine (albeit counting only the first appearance) when I used, COUNT, so this should not be aproblem I hope.