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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.