Explanation
In this example, the goal is to create a clickable link that will result in a ready-to-send email.
The mailto link protocol
The mailto link protocol allows five variables as shown in the table below:
| Variable | Purpose |
|---|---|
| mailto: | The primary recipient(s) |
| &cc= | The CC recipient(s) |
| &bcc= | The BCC recipient(s) |
| &subject= | The email subject text |
| &body= | The email body text |
Notes: (1) separate multiple email addresses with commas. (2) Not all variables are required.
The variables are presented as “query string parameters”, delimited with the ampersand (?) character. For example, a fully formed mailto: link in an HTML document might appear like this:
When a user clicks the link text, a new email will open in the default email application with the variables filled in.
Creating the link
In Excel, the HYPERLINK function can be used to create links. The basic syntax is:
=HYPERLINK("link","link text")
The link itself is a text string that represents a valid link. The link text (called “friendly name” in Excel) is the text displayed to a user.
Ultimately, the goal for the formula in G5 is to build a string like this:
mailto:aya@aa.com?cc=bb@bb.com&subject=subject&body=body
Because the mailto link uses several pieces of information, it must be assembled with concatenation .
The formula is a bit tricky. While the ampersand is the operator for concatenation in Excel, it is also used to delimit the mailto link parameters (cc, bcc, subject, etc.). This means that some ampersands (&) are used to join text in the formula, and some are embedded in the final result . In the code below, the ampersands in yellow are used for concatenation in Excel. The white ampersands are embedded in the final result:
=HYPERLINK("mailto:"&C5&"?"
&"cc="&D5
&"&subject="&E5
&"&body="&F5,
"link")
Note: the formula above is entered with line breaks for better readability.
Empty mailto parameters
For the sake of simplicity, the formula above does not try to exclude empty parameters from the final result. In quick testing with Gmail and Outlook, missing parameters seem to be ignored gracefully. The behavior in other email applications may vary.
Explanation
The FORMULATEXT is fully automatic. When given the reference of a cell that contains a formula, it will return the entire formula as text. In the example as shown, the formula:
=FORMULATEXT(C5)
returns the text “=IF(B5>=70,“Pass”,“Fail”)”.
Dealing with errors
The FORMULATEXT function will return the #N/A error when a cell does not contain a formula. To trap this error and display nothing when a cell does not contain a formula, you can use the IFERROR function like this:
=IFERROR(FORMULATEXT(A1),"")
Alternately, you can use ISFORMULA and IF like this:
=IF(ISFORMULA(A1),FORMULATEXT(A1),"")
Checking for specific text
To check a formula for a specific text, you can use the ISNUMBER and SEARCH functions. In the formula below, we are checking a formula in A1 to see if it contains “apple”:
=ISNUMBER(SEARCH("apple",FORMULATEXT(A1)))
The result is either TRUE or FALSE. See this page for a full explanation.
