Hi guys, I want to count the number of commas in a field, what should I do?
In the image I have 4 commas, but I want it to count when it reflects more or less than 4
This is the only way I could think of but it has it’s limitations.
What you could do is check up to a certain number (like 10) and see if there is a string after splitting on a comma.
ifelse(split({string},‘,’,10)<>‘’,10,split({string},‘,’,9)<>‘’,9,split({string},‘,’,8)<>‘’,8,split({string},‘,’,7)<>‘’,7,split({string},‘,’,6)<>‘’,6,split({string},‘,’,5)<>‘’,5,split({string},‘,’,4)<>‘’,4,split({string},‘,’,3)<>‘’,2,split({string},‘,’,1)<>‘’,1,0)
If you have 11 commas it will return 10. So you would need to go up to a number you’re comfortable with there not being extra commas.
I wish there was a way to get the length of an array / list but there is not.
Hope this helps
2 Likes
Hello Max, thank you very much, the function works well when there are commas, when the string does not have commas it returns an inconsistent value
Maybe you should wrap the calculation in an ifelse before to check if there is a comma.
ifelse(locate({string}, ‘,’)>0,{calculated field},0)
1 Like
Yes, that’s how I was doing it, thank you very much, it was correct because I only need to count 9 commas maximum
A more generic approach to count the occurrence of a character or any (non-overlapping) strings within a field is:
strlen({field})-strlen(replace({field},'<SEARCH_STRING>',''))
In your case, you can count the number of commas in your Tags field using the following calculation:
strlen({Tags})-strlen(replace({Tags},',',''))
2 Likes