We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calc Mailto need to enter a £ symbol

asked 2020-02-25 23:57:41 +0200

jcs94782 gravatar image

updated 2020-02-27 12:11:15 +0200

I am using Calc to generate an email via a "mailto" Hyperlink. The email hyperlink includes a Mailto section, a Subject section and a body section. Various parts of the email are created through formulae on the spreadsheet.

This all works apart from an annoying problem. I need to include a UK currency symbol = £ in the email body. This £ symbol is converted into the characters £30 in the generated email. I need it to show £30 without the "Â" character.

Is there any way to do this?

If it is relevant I am using Microsoft LiveMail as my email client. Yes I know it is no longer supported but it works. I can't find another client that allows me to take a daily backup of individual emails. All other clients put all the emails into a single email files which is too large to backup.

*Update:* I have simplified my problem.

Here is the code entered into the cell:

=HYPERLINK("mailto:abb@somewhere.com?subject=test&body=a message containing £30 etc")

When I use CTRL + Click my mail client LiveMail opens and displays

a message containing £30 etc

I am using Windows 10 Home edition, version 1809, build .18363.657 I am using UK Locale. My email client is Microsoft Livemail. I cannot find any language settings I assume it is using the OS default My LibreOffice language settings are all UK LO Version: (x64)

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-02-26 13:31:17 +0200

petermau gravatar image

updated 2020-02-27 18:28:00 +0200

You mention the £ (pound sign). I expect you have a similar problem with the € (euro). It is a problem with the non-LibreOffice settings on your system.

You do not describe the basics. For example Your operating system, version, language settings, the settings for the “mailto” etc. Having said that the problem is probably with your settings, these will include the “mailto”, cut and paste.

LibreOffice uses the multilingual International standard Unicode. (UTF-8). Your “mailto”, cut/paste, e-mail is probably set to US-ASCII or Windows-1252. The £30 is the Unicode (UTF-8) being shown as three 8-bit characters. They may also be shown as � (character error)

I am afraid that I cannot set up your problem, but hope these notes may start you off in the right direction.


Glad to see you have a bypass, but I would prefer to fix the cause.

LibreOffice and the Internet default to the Unicode International Character set. The headings, including address, subject etc support Unicode. Unicode includes US-ASCII (seven bit 0-127 characters), not the pound sign and ISO-8859-1 (8 bit 0-255 characters) which includes the £ (pound) sign. Unicode currently supports about 138,000 characters.

LibreOffice uses Unicode (UTF-8) whatever the language settings, thus your problem is not there. However, although you have a Windows system defined as English-UK, and which should support the £ sign, MS systems and applications do not always default to their expected settings. In particular MS e-mail settings often default to American US-ASCII, unless you carefully correct this problem. I know several UK Outlook users whose language settings are US-ASCII. An the cut/paste defaults to US-ASCII or Windows-1252. This could be the cause of the problem.

Although %A3 may get you around your Pound problem. It does not seem very elegant. I hope you can find the MS settings to really sort out this problem.

edit flag offensive delete link more


I have updated my original post with more information

jcs94782 gravatar imagejcs94782 ( 2020-02-27 12:12:02 +0200 )edit

answered 2020-02-27 15:41:20 +0200

jcs94782 gravatar image

I have managed to solve my problem - here was my initial hyperlink

=HYPERLINK("mailto:abb@somewhere.com?subject=test&body=a message containing £30 etc")

I knew it was a character code problem. I eventually found that replacing the "£" sign by "%A3" made it work.

To make my code more readable, and to allow for more than one instance of an email body I actually used the substitute function to generate the actual hyperlink

 =HYPERLINK(substitute("mailto:abb@somewhere.com?subject=test&body=a message containing £30 etc","£","%A3")

The actual hyperlink actually repeats this substitution technique to be able to replace the recipient email address, their name and a subject into one of a number of message templates.

edit flag offensive delete link more


Better use ENCODEURL that is designed just for this task:

=HYPERLINK("mailto:abb@somewhere.com?subject=test&body=" & ENCODEURL("a message containing £30 etc"))
Mike Kaganski gravatar imageMike Kaganski ( 2020-02-27 20:08:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-25 23:57:41 +0200

Seen: 112 times

Last updated: Feb 27 '20