Getting Excel to Recognise an Email Address

Converting it into a hyperlink

Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.

When an email is a hyperlink it is clickable – when you click the hyperlink it opens your default email app and allows you to send an email.

The first technique, if there aren’t many emails, is a simple keyboard sequence.

Select the cell and then press the F2 function key and then press Enter. If you have a list keep pressing that combination in sequence – you will be amazed at how fast you can get – you can convert a lot of cells in a minute or so.

This assumes your Enter key moves down the column.

Before and after image below.

Emails conversion

The other method if you have thousands of email addresses is the macro below – it will do the trick for you – it will work on smaller ranges too if you don’t want the keyboard practice.

Select the range containing the email addresses and then run the macro.

Sub Convert_Emails()
' Converts email addresses from text into hyperlinks
Dim c As Range
For Each c In Selection
 
   ActiveSheet.Hyperlinks.Add Anchor:=c, Address:="mailto:" & c.Value
 
Next c
End Sub

If you are unsure how to use macros check out this blog post.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *