Counting the amount of numbers in a column

Platform discussions
1

Hi there,
I have one column which stores ID numbers - here’s an example:
“#23323, #785, #209809”

I’m trying to create a formula column to count the number of ID numbers (so for the above example it would return “3”), but I’m having difficulty.

Can anybody suggest any solutions?

So far I’ve tried replacing the hashtags with spaces and using the COUNT() function like this:
COUNT(SUBSTITUTE({ID Numbers}, “#”, " "))
but that just returns either “1” regardless of how many IDs there are, or an error.

I’ve looked through the other options for formulas but can’t find anything that works. Any suggestions would be much appreciated!

2

Hi @ollieBreathworks - while I figured the below would work…it doesn’t, which is frustrating.

LEN({ID}) - LEN(SUBSTITUTE({ID},",",""))

@JCorrell Is the Formula King - so hoping he can assist.

Mark

3

@ollieBreathworks @mark.anley

The monday SUBSTITUTE() function will not substitute a null string. So, to use it to count, you have to go the other way:

LEN(SUBSTITUTE({ID}, "#", "XX")) - LEN({ID})

Jim - Subscribe to The Monday Man
Watch Our Latest Video: Get Rid of Subitems

2 Likes
4

You’re the man @JCorrell! Thank you.
@ollieBreathworks This should take care of your question.

5

Very clever, thanks so much @JCorrell @mark.anley!!

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.