PDA

View Full Version : Solved: www.domain.com TO name@domain.com



Kindly_Kaela
11-30-2007, 04:51 PM
Hi everyone!

Importing data from SQL to Excel, resulting in a long list of email addresses. I want to get rid of the name@ and make the domain name a hyperlink.

Some cells have no email address. In that case, I would like the 'hyperlink-cell' left blank and highlighted yellow (to manually type in later).

The entire code would be greatly appreciated, I'm still a novice :)

Happy Holidays!
:cloud9:

mikerickson
11-30-2007, 04:58 PM
If "Name@Domain.Com" is in A1,

=LEFT(A1,SEARCH("@",A1)-1) will return "Name"
=MID(A1,SEARCH("@",A1)+1,1000) will return "Domain.Com"


ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Selection.Value will create a hyperlink to the site named in the Selected cell.

Kindly_Kaela
11-30-2007, 07:05 PM
Sorry, don't understand. Could you please write out the full command line? Let's assume all the emails are in column A and I want to fill in column B with the HTML.

And if a cell doesn't have an '@', then its left blank.

mikerickson
11-30-2007, 08:37 PM
Put this in B1 and Fill Down.
=MID(A1,IF(ISERROR(SEARCH("@",A1)),LEN(A1)+1,SEARCH("@",A1)+1),1000)

unmarkedhelicopter
12-01-2007, 03:32 AM
Just to be sure, give an example of what you'd find in column A ?
and what would you want in column B ?

RichardSchollar
12-01-2007, 10:40 AM
Hi

The following formula in B1 copied down will insert the hyperlink for you against those cells containing email addresses:

=IF(ISNUMBER(FIND("@",A1)),HYPERLINK("www." & MID(A1,FIND("@",A1)+1,255)),"")

Richard