R
Rudeseal
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]#
or
APWD15E#http://www.frigidaire.com/product.aspx?productid=2235#
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. Just examples of how you would handle a cell that either does not
have any data in it or if you want to make sure that people know that there
is NO hyperlink related to that particular record.
=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 examples will show up as the friendly name underlined:
Mary Joe Cleaver
APWD15E
Enjoy!
Bob R.
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]#
or
APWD15E#http://www.frigidaire.com/product.aspx?productid=2235#
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. Just examples of how you would handle a cell that either does not
have any data in it or if you want to make sure that people know that there
is NO hyperlink related to that particular record.
=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 examples will show up as the friendly name underlined:
Mary Joe Cleaver
APWD15E
Enjoy!
Bob R.