Calc Mailto need to enter a £ symbol

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.

I have simplified my problem.

Here is the code entered into the cell:

=HYPERLINK(" 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)

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.

I have updated my original post with more information

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

=HYPERLINK(" 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(" 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.

Better use ENCODEURL that is designed just for this task:

=HYPERLINK("" & ENCODEURL("a message containing £30 etc"))