Q: How do I force the text to be an e-mail address without having to play about?

Problem: I had an Excel spreadsheet with email addresses. I wanted to add the mailto: hyperlink to a range of cells. The answer came from Suat at experts-exchange.

Goto Excel VBA (Alt+F11) and Insert->Module to add a new module then copy And paste the following code into this new module. Then return excel and select the range which has email addresses (you can select other cells, you don't have to use multiply selection) and then run the macro by pointing Tools->Macros->Macros and selecting ChangeToEmail macro. Code will look into selected range and change the cell to a hyperlink if cell has an email address in it by using cell value as email address.

'—-Code Start—
Sub ChangeToEmail()
Dim rng As Range
Dim cll As Range
Dim tmpval
   Set rng = Selection
   For Each cll In rng.Cells
       tmpval = cll.Value
       If InStr(cll, “@”) > 0 Then cll.Hyperlinks.Add cll, “mailto:” & cll.Value
       cll.Value = tmpval
   Next cll
End Sub
'—-Code End—