Count the numbers in a text column

[Qotsa]

Hi,

I have a text column that contains values like -

Text Column

1,2,3,4,5

6,7

1,5,6

For above I would like to return -

5

2

3

Is there a way I can do a count of the numbers? Every number is always seperated by a comma.

[v-chuncz-msft]

@Qotsa 

By the way, you might use STRING_SPLIT in Transact-SQL if necessary.

SELECT [Column1]
	, (
		SELECT COUNT(1) 
		FROM STRING_SPLIT([Column1], ',')
	) AS [Count]
FROM [Table1]

[Ashish_Mathur]

Hi,

Taking a cue from the solution shared by hocine satour to a similar problem on  my Blog article here, this M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each List.Count(List.ReplaceValue(Text.Split([Text], ","), " ", "", Replacer.ReplaceText)))
in
    #"Added Custom"

Hope this helps.

[az38 · Accepted solution]

Hi @Qotsa 

create a calculated column

Column = 
PATHLENGTH(
SUBSTITUTE([Text Column];",";"|")
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn