Text & String Cleaning
Build Simple mailto: Links and Understand URL Encoding
Build a basic mailto: string from restricted simple ASCII inputs, and learn when full percent-encoding is needed for subject and body text.
Build a basic mailto: string for simple ASCII values
This worksheet builds a basic mailto: string for the shown simple example, replacing spaces in the subject and body with %20. It is not a general URL encoder: reserved characters such as &, ?, # and %, newlines, and non-ASCII text need correct percent-encoding, including UTF-8 encoding. For example R&D must be encoded as R%26D; leaving & unescaped may split the query into additional parameters. Do not use this example with arbitrary or untrusted text without a proper URI encoder.
The check for @ is only a minimal missing-symbol check; it is not email-address validation. TRIM alters repeated and surrounding spaces. On Excel for Windows ENCODEURL can encode field values, but Microsoft says ENCODEURL is not available in Excel for Mac or the web; choose an encoder available in your environment.
| Email Field | Spreadsheet Cell Input | RFC-6068 Encoded Component |
|---|---|---|
| Recipient To (B2) | ap@northwind.io | mailto:ap@northwind.io? |
| Optional CC (B3) | cfo@northwind.io (or blank) | cc=cfo@northwind.io& (omitted if blank) |
| Subject Line (B4) | Invoice 1042 Past Due | subject=Invoice%201042%20Past%20Due |
| Short Body (B5) | Please remit balance | &body=Please%20remit%20balance |
Basic mailto: Builder (Spaces Only; Restricted Inputs)
Checks for an @ symbol and replaces spaces in the shown subject and body. Use only with the simple sample values; this does not validate addresses or handle arbitrary reserved and Unicode characters.
=IF(ISERROR(FIND("@", B2)), "Invalid Recipient Email", "mailto:" & TRIM(B2) & "?" & IF(OR(ISBLANK(B3), TRIM(B3) = ""), "", "cc=" & TRIM(B3) & "&") & "subject=" & SUBSTITUTE(TRIM(B4), " ", "%20") & "&body=" & SUBSTITUTE(TRIM(B5), " ", "%20"))Count Characters in the Generated Example URI
Counts the characters in this basic string. Link length and supported URI handling depend on the spreadsheet and email client; a character count does not validate a mailto: URI.
=LEN("mailto:" & TRIM(B2) & "?" & IF(OR(ISBLANK(B3), TRIM(B3) = ""), "", "cc=" & TRIM(B3) & "&") & "subject=" & SUBSTITUTE(TRIM(B4), " ", "%20") & "&body=" & SUBSTITUTE(TRIM(B5), " ", "%20"))Try the example
Use the original values or change them to test your understanding.