count words separated by comma
how can I count specific open ports that are separated by a comma?
Comments
-
Try something like this...
=LEN([Open Ports]@row) - LEN(SUBSTITUTE([Open Ports]@row, ", ", "")) + 1
Basically you are finding the total number of characters (including spaces) and subtracting from it the number of characters once all of the comma/space's are removed. You then add one to account for the final entry, and there you have it.
-
Hi Paul,
I tried this and the "@row" was giving me an error, once I removed it, then the formulat worked. thanks.
In my case it works, I just need to figure out now when the cell is blank to return a 0 because it is returning a 1.
thanks again for posting this.
Jm
-
You may have already figured this out.
You can wrap Paul's formula in an if-isblank handler to get the 0. =IF(ISBLANK([OPEN PORTS]@row), 0, LEN([OPEN PORTS]@row) - LEN(SUBSTITUTE([OPEN PORTS]@row, ",", "")) + 1)
@Paul Newcome is the SmartSheet guru!
-
Tried to use this for names (first and last, first and last) and it's not working. :(
