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

Options

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!

Answers

  • Jason P
    Jason P ✭✭✭✭
    Options

    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?

    Cheers.

    Forever forwards Backwards never.

  • CincyErmin
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!