# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Concatenate to form a hyperlink

## CPixie

Hello everyone,

I'm new to this forum and only use Excel for very basic things, so my knowledge is limited. I was wondering if someone could help me out on something:

I'd like to make a "clickable database" of a list of files and need to concatenate 2 text cells into a hyperlink.

Example:
A1 is "http://www.1234.com/"
A2 is "abc.jpg"
A3 concatenates A1 and A2 into "http://www.1234.com/abc.jpg"

it all works fine for the text. But how can I turn the concatenated text into a clickable hyperlink?


Thanks
CPixie

----------


## Mikeopolo

Remove the http stuff and start at www.....

Then click to follow the link, click and hold to edit the cell. 

Regards
Mike

----------


## nath4564

I have asked a similar question in excel programming forum. It can't be done via concatenating as excel will not pick the value up as a link, no matter how it is written....

The key is a macro or VB scripting of some kind, keep an eye on responses to my question if you have no luck here.

----------


## nath4564

hi ignore my last comment, I just figured it out.

answer is:

"=hyperlink(concatenate(A1,A2))"



Regards,
Nathan

----------


## gpenguins

Can someone tell me if this is poosible with file names?  That is, I have a main project list that I want to hyperlink to individual Excel project files.  So, cell A1 has the project number, I made a hidden cell B1 that was just the path, then did:

=HYPERLINK(CONCATENATE(B1,"\",A1,".xls")) 

- which then shows me what I want to see on the page (that is the whole link to the Excel file), but when I click on it it tells me "Cannot Open Specified File".  I know I can easily individually set up hyperlinks as the files are created, but this is being created for a less experienced user for whom I want to make it as easy as possible.

Edit - also, just in case (but not expecting anything), I also tried to hyperlink without using the path and only doing the project name with the ".xls" concatenated, and that didn't work either.  

Edit Again - the problem seems to be that I have a hyphen in the project name so it's expecting it to act as a minus sign (I only know this because I took the hyphen out and it worked fine, so I tried doing a fully typed link with the hyphen in it and that's when I got the minus sign error) - for some reason this is completely stumping me right now.  Do I therefore have to remove the hyphens from my file names?

Any help is greatly appreciated.

Thanks,
Dave

----------


## Talus

I just ran across this thread today while doing a search for a similar problem I had myself.

I got the active links working by doing the following:

Use =concatenate to create the URL in one column
Use =hyperlink in another column to reference the concatenated cells

e.g.
Column A: file number
Column B: =concatenate("file://server/folder/",A2,".pdf")
Column C: =hyperlink(B2)

I hid the concatenated column to make it look nicer.

----------


## timkatje

While yes, the simplest method is to use concatenate, while spell out the word at all, just use the & symbol...

Example:
Column A = Scheme (aka, http://, ftp://, file://, etc)
Column B = Host (or file location, etc)
Column C = Port (leave blank if none)
Column D = Path (directory structure, leave blank if none)

Column E =HYPERLINK(A1&B1&C1&D1)

As simple as that.  No separate but hidden concatenated columns.  
And if you'd prefer to have the full URL hidden in the final column, and just have a hyperlink display "LINK", use this:

Column E =HYPERLINK(A1&B1&C1&D1, "LINK")

It doesn't have to be as broken down as this either, if all of your files are http for example, build it into your hyperlink command.

Column E =HYPERLINK("https://"&B1&"/"&D1)

The really cool thing about the first example is if those columns are completely blank (aka, no spaces or any other characters), they won't even show up in the final hyperlink.

----------


## bizwizkid

I'm trying to complete this function and am having some issues. 

I'm trying to create a link to allow me to click and view the results in a browser window to verify the non-profit status of a school. The school in the below example isn't in their database. Note: I used words from their name and city and put them in the string for this site to get these results - which work fine. My problem is getting the hyperlink to work as I have hundreds of these queries to give my volunteers to do.

Here's my data:

Col AG5    http://www.irs.gov/app/pub-78/search...s=false&names=
Col AE5   AAUW+Nursery
Col AH5   &nameSearchTypeAll=true&city=
Col AF5   Waukegan
Col AI5    &state=All...&country=USA&deductibility=all&dispatchMethod=search&searched.nameSearchTypeStarts=false&searched.names=school&searched.nameSearchTypeAll=false&searched.city=&searched.state=All...&searched.country=USA&searched.deductibility=all&searched.sortColumn=name&searched.indexOfFirstRow=4000&searched.isDescending=false&submitName=Search
________________

Here's my cell where I'm trying to get an active hyperlink I can click and launch my browser. 

Col AJ5  =HYPERLINK(AG5&AE5&AH5&AF5&AI5)

This returns a #VALUE! error which is an active hyperlink that doesn't do anything when clicked in Firefox.
_________________

To prove the links should be working here's my results when I concatenate the data with: =CONCATENATE(AG5&AE5&AH5&AF5&AI5). This gives me that statement that there are no non-profits that fit my keywords: "There were no exempt organizations found matching the search values you entered. Please refine your search and try again." I have tried manually copying/pasting the final urls for several rows and get both positive and negative search results which is my intent. Here's the desired end URL:

http://www.irs.gov/app/pub-78/search...mitName=Search
______________

I tried removing the http:// and http://www. portions and including that in the concatenate string but, that isn't working either.

Your help would be greatly appreciated as we've got lots of schoolchildren to help. Thanks much!









> While yes, the simplest method is to use concatenate, while spell out the word at all, just use the & symbol...
> 
> Example:
> Column A = Scheme (aka, http://, ftp://, file://, etc)
> Column B = Host (or file location, etc)
> Column C = Port (leave blank if none)
> Column D = Path (directory structure, leave blank if none)
> 
> Column E =HYPERLINK(A1&B1&C1&D1)
> ...

----------

