count words separated by comma

Tribeiro

how can I count specific open ports that are separated by a comma?

 

 

Paul Newcome

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.

Jorge Montoya

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

David Fiorino

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!

Valerie_WPA

Tried to use this for names (first and last, first and last) and it's not working. :(