I am setting up a workflow and would desire when a user pastes or types an email into any cell within a column it is automatically converted to a mailto: hyperlink.
Currently, I have the "email" column of cells with data validation set to "is valid email". Then I have the column of cells to the right of that with the code below. Once working this automation column will be hidden so it just works in the background.
=IF(ISBLANK(ADDRESS(ROW()-1,COLUMN())), "", HYPERLINK(CONCATENATE("mailto:",ADDRESS(ROW()-1,COLUMN()))))
The results I get is: The email cell validates and only allows valid emails but is not converted to hyperlink. The code cell returns the following: mailto:$N$1
