+ Reply to Thread
Results 1 to 4 of 4

Hyperlink from plain text.

  1. #1
    B.C.Lioness
    Guest

    Hyperlink from plain text.

    I am importing an Access Query into a sheet of my Excel file. This query has
    a column that is all hyperlinks. My problem is that instead of importing as
    a hyperlink, it comes into Excel as plain text. I have used HYPERLINK(A1) to
    try to convert, but there is a number sign (#) at the start of the plain
    text. Is there a way I can delete this first character, then use
    HYPERLINK(A1) to get my hyperlink into a column that is unaffected by data
    updates?
    I am using Access and Excel 2002, and I am pretty new to all this, I am
    currently trying to wrap my brain around macros to see if I can find a
    solution there, but that is confusing me more I think.

    I will be grateful for any help.

    Lori.

  2. #2
    Dave Peterson
    Guest

    Re: Hyperlink from plain text.

    How about just skipping the first character in your =hyperlink() formula:

    =hyperlink(mid(a1,2,255))

    (make that 255 large enough for any of your hyperlinks.)

    ===
    Or if the # sign only appears in the first character in those strings, you
    could:

    Edit|Replace
    what: #
    with: (leave blank)
    replace all


    B.C.Lioness wrote:
    >
    > I am importing an Access Query into a sheet of my Excel file. This query has
    > a column that is all hyperlinks. My problem is that instead of importing as
    > a hyperlink, it comes into Excel as plain text. I have used HYPERLINK(A1) to
    > try to convert, but there is a number sign (#) at the start of the plain
    > text. Is there a way I can delete this first character, then use
    > HYPERLINK(A1) to get my hyperlink into a column that is unaffected by data
    > updates?
    > I am using Access and Excel 2002, and I am pretty new to all this, I am
    > currently trying to wrap my brain around macros to see if I can find a
    > solution there, but that is confusing me more I think.
    >
    > I will be grateful for any help.
    >
    > Lori.


    --

    Dave Peterson

  3. #3
    B.C.Lioness
    Guest

    Re: Hyperlink from plain text.

    Thank You so much, the MID function worked perfectly!

    "Dave Peterson" wrote:

    > How about just skipping the first character in your =hyperlink() formula:
    >
    > =hyperlink(mid(a1,2,255))
    >
    > (make that 255 large enough for any of your hyperlinks.)
    >
    > ===
    > Or if the # sign only appears in the first character in those strings, you
    > could:
    >
    > Edit|Replace
    > what: #
    > with: (leave blank)
    > replace all
    >
    >
    > B.C.Lioness wrote:
    > >
    > > I am importing an Access Query into a sheet of my Excel file. This query has
    > > a column that is all hyperlinks. My problem is that instead of importing as
    > > a hyperlink, it comes into Excel as plain text. I have used HYPERLINK(A1) to
    > > try to convert, but there is a number sign (#) at the start of the plain
    > > text. Is there a way I can delete this first character, then use
    > > HYPERLINK(A1) to get my hyperlink into a column that is unaffected by data
    > > updates?
    > > I am using Access and Excel 2002, and I am pretty new to all this, I am
    > > currently trying to wrap my brain around macros to see if I can find a
    > > solution there, but that is confusing me more I think.
    > >
    > > I will be grateful for any help.
    > >
    > > Lori.

    >
    > --
    >
    > Dave Peterson
    >


  4. #4
    Registered User
    Join Date
    08-28-2009
    Location
    Great Falls, MT
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Hyperlink from plain text.

    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:

    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.

    =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:

    APWD15E

    Enjoy!
    Bob R.
    Last edited by Rudeseal; 08-28-2009 at 04:48 PM. Reason: fixed hyperlink example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1