I get duplicate values when I convert a hyperlink field to text

Every time I import a query containing email addresses from Access into Excel, I end up with a "rider" on each email that looks like this: "[email protected]#mailto:[email protected]#". I tried to overcome the problem by making a new table with the email field changed from hyperlink to text, but the result was the same as above. Does anyone know why this happens and how to prevent it?

Ok, after much head beating, I have figured this out..

I have an excel spreadsheet where I have a data connection to an ACCESS Database, one of the tables is hyperlinks. The hyperlink comes across as text only in this format:

Mary Joe Cleaver#mailto:[email protected]#

The Hash "#" shows the URL and the front is the "Friendly Display Name" of it.. I had to break this apart in order to put it into the =Hyperlink()

The First version will insert the word NONE into the cell if there are no hashes, which would mean no hyperlink, the second one will simply leave the cell blank.

=IF(ISERROR(FIND("#",A1)),"NONE",(HYPERLINK(SUBSTITUTE(MID(A1,FIND("#",A1,1)+1,99),"#",""),MID(A1,1, FIND("#",A1,1)-1))))

=IF(ISERROR(FIND("#",A1)),"",(HYPERLINK(SUBSTITUTE(MID(A1,FIND("#",A1,1)+1,99),"#",""),MID(A1,1,FIND ("#",A1,1)-1))))

Change the A1 to whatever cell your imported or table from access is that has the hyperlink info.

This particular hyperlink will show up as the friendly name underlined:

Mary Joe Cleaver

Enjoy!
Bob R.