How can I count only the text in a column that is hyperlinked?

CincyErmin

Hello:

In our Project Portfolio sheet, we are setting up hyperlinks on the job names that have an existing project plan. Our goal is to create project plans for all of the smaller projects for reporting. I've been trying to find a way to count the cells in the Project Name (primary) column if it is missing a link. Has anyone been able to do this?

Thank you!

Jason P

Hi @CincyErmin

I'm no Guru on Formulas but I have a simular method of what I think you're looking for. Smartsheet don't currently a way to search for Hyperlink. As a work around could you count what's not there!

COUNT Cells.

Look for a common character set in you hyperlinks, in our case its "SCG" i.e.: =COUNTIFS([Project name]:[Project name], NOT(CONTAINS("SCG", @cell)), [Project name]:[Project name], <>"") Pop this formula in a new column.

The CONTAINS function tells it to counts cells where SCG is not present regardless of where SCG sits in a string typical of a hyperlink i.e. \\TBCABCSCG\\. If SCG did not appear anywhere in this example the result is 1. The [Project name]:[Project name], <>"") part means don't count blank cells.

Other Option is to Non highlight the cell! Conditional formatting. Set Project Name column as your condition, set criteria as contains (enter your hyper character set). Format for cell highlight and apply to Project Name column. Any cell not containing your character set will be white like in this example. Unfortunately Smartsheet does not have a "Does not contain" as a condition so this is the work around.

Would either work for you?

CincyErmin

Hi @Jason P:

Conditional formatting is not an option for us as we need a count for reporting.

Regarding the formula, I copied and pasted into the Sheet Summary as is. It returned 113 (there are 113 rows) and when I removed the NOT statement, it returned 0. I also played around with changing SCG (even to "") with no change. It is only counting if there is text in the cell and not considering if there is a hyperlink associated with the text.

Thank you!

Jason P

Hi @CincyErmin

Conditional formatting: Used only to highlight cells missing the string of letters, difficult to find those cell/s otherwise in 50+ rows.

As noted there is no way to count hyperlinks. The formula is simply looking for a string of letters, in our case SCG is the common string, but it could be Ron, Hermione or Harry… In our case its SCG. \\SCG-FS\SCG-Company$\_Quote Files\293501 - 294000\293886 - API\03-Construction.

Cell can contain anything 123, ABC, XYZ *^% - makes no difference because it's only counting cells with whatever you have placed between the " ??? "

I took the formula from the count column (in my case missing link) and placed it in a sheet summary field, I have no issues with counts with our without NOT and regardless of what I put between " " the counts are true.