Solved: www.domain.com TO name@domain.com

[Kindly_Kaela]

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!

[mikerickson]

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]

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]

Put this in B1 and Fill Down.
=MID(A1,IF(ISERROR(SEARCH("@",A1)),LEN(A1)+1,SEARCH("@",A1)+1),1000)

[unmarkedhelicopter]

Just to be sure, give an example of what you'd find in column A ?
and what would you want in column B ?

[RichardSchollar]

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