Excel 2007 : Hyperlink Function

I received a #VALUE! error when I tried to set up a hyperlink linking to a web site location in my Excel spreadsheet cell . It appears the URL contain more than 255 characters an that is creating the error. Is there any way to get around it. I tried to use the CONCANTENATE splitting up the strings, which also did not help. Anyway to get around this problem? Thanks in advance.

Thanks. It worked.
However, it is a bit consuming to do it for large cases. My application is a bit more complicated, as I have to create a database with over 2500 lines of URL references. In an application, which I did, where the URL reference line was less than 100 characters, the exercise was very easy. First for a cell, I used the CONCANTENATE function with two string variables and a cell reference (string1, cell reference, string2) to a field say ‘symbol’ to create the URL reference line . Symbol variable field contained the names which was the only variable which changed from one row to the next in the URL reference field. Once the CONCANTENATE was created for the first line, I just had to just copy the contents to all the 2500 lines to get the url reference for each symbol, a case of Just a matter of seconds.
For me the approach that worked was to copy the URL from the website to a Word document, which can be hyperlinked just by clicking the enter key after copying. Then Copy the activated URL to the excel sheet cell. However this exercise is also as time consuming as the tinyurl.
Another exercise that I tried did not work either. Using the above approach, first I created the cell with the URL reference in line 1. This works. I, then, copied the same to the URL field of the second line. Then I used the ‘Find and Replace’ approach to find the symbol and replace it with the symbol corresponding to the second line. However when I clicked on it, it is not taking me to the URL reference to the second line. It is still linking to the URL reference in the first line. This is very strange & puzzling. I could not find an answer for that. If this worked, it would have been less time consuming than either the tinyurl approach or the Word document approach as I had to just replace the symbol variable for each line.
Sorry for the long discussion of the problem. I would appreciate if anybody can suggest a faster approach than either the tinyurl or word document approach.
Thanks

Your original post did not indicate that you had thousands of these, it just referred to "a hyperlink." I agree that anything manual like tinyurl is unworkable here. However, I have not bumped into this limit before and am unaware of how to manage it.

Originally Posted by vchesar

...Then I used the ‘Find and Replace’ approach to find the symbol and replace it with the symbol corresponding to the second line. However when I clicked on it, it is not taking me to the URL reference to the second line. It is still linking to the URL reference in the first line.

The actual URL for the link is stored separately from the text that is displayed for that link. Find & Replace only works on the displayed text, not the underlying link. That kind of editing of the links would have to be done using VBA.

In Excel 2007 (as in your profile) the limit on characters in a cell is 32,767, so that's not your problem. I am surprised that this doesn't work if you put it directly into Excel, but works if you copy it from Word first. Are you actually using the HYPERLINK function, or do you just mean that you are inserting URLs that become hyperlinks?

If I can get a spare moment I'll look further. Could you provide one example of a long URL to use as a test case?

Thanks for the continuing response. I can give you an example of the type of the exercise that I am doing, using the sample given under http://notlong.com/sample/ (I used address 1 instead of the 2801 in the sample):
http://maps.yahoo.com/py/maps.py?Pyt...addr=1+Ocean+P ark+Blvd&city=Santa+Monica&state=CA&csz=Santa+Monica,+CA+90 405-5200&slt=34.018220&sln=-118.457158&name=&zip=90405-5200 &country=us&&BFKey=&BFCat=&BFClient=&mag=6&desc=&cs=9&ne wmag=8&poititle=&poi=&ds=n
In the example that I am discussing, I am using the Excel workbook with two sheets. The second sheet of Excel workbook would have three fields – Street Address, a friendly link name field and link location. The link location would be same as above. The first sheet would have two fields - the street address field & the URL_link field. The street address field in both sheets would have all the addresses associated with the Ocean Park Blvd street. In this example, I have used two sheets as I want to keep the URL references in separate sheet. There are many other characteristics that can be associated with the property address (location map is only one), each of which have a URL reference. For simplicity, only one characteristic is used in this example. Each of the characteristic would have a separate URL Reference sheet. I did not want to crowd the sheet 1 with this information. Sheet 1 would have fields linking it to each of the characteristic in other sheets using the Hyperlink function.
For line 1 in sheet 2, I start with Street address field, Sheet2!A1, which will have the value – 1 for the above link. All the rows below this will have the other street numbers associated with Ocean Park Blvd. It would have been very easy exercise to create links for all addresses if I could have been able to use the CONCANTENATE function as I discussed in my first post to create the link reference :
= CONCANTENATE (“http....addr=”, sheet2!A1, “+Ocean.....&ds=n”). Copying this cell to all other cells under the link-_location’ field, if it worked, would have given link locations for all the addresses in the database in a matter of seconds. Unfortunately it does not work for long url. In my applications, it did work for cases where the url was shorter.
The cell, sheet1! B1, uses the Hyperlink function ‘=Hyperlink(“#Sheet2!C1”, Sheet2!B1). This helps to jump to the sheet2, row 1 by clicking on this cell. And then clicking on Sheet2!C1 would take it to the Google Map showing the address location 1 Ocean Park Blvd.
By the way, I never knew that if you have a cell with a hyperlink (not using the HYPERLINK function), the F&R will not modify the URL that you see. Thanks for educating me.
Again, I would greatly appreciate if anybody can suggest a faster approach than either the tinyurl or word document approach.

Hello,
I just learned that the Hyperlink exercise is much for user friendly in Word than in Excel. Unlike Excel, Word can handle not only long url but also edit the same. In Word, after selecting the Hyeperlinked text, one could remove the Hyperlink, edit it and then Hyperlink again. This option allows the user, thus, to develop a database table first in Word and then copy the same to Excel sheet. The Word Table will have two columns – first for address and second for link location. Once you enter the link location for the first address, one has two options depending upon link is hyperlinked or not. If Hyperlinked, remove the Hyperlink, and then copy the same to all the cells and then these can be edited to replace the address using the address from the corresponding address field cell. If the cell is not Hyperlinked , just copy the cell content to all the cells and do the editing. Once the editing is done, the cells can be hyperlinked again
It is surprising the Excel Hyperlink does not have the editing option. Or am I missing something. I find this approach, though slower than CONCANTENATE if it worked,is less time consuming than using the tinyural approach. Anyway comments, if any, would be greatly appreciated.
Thanks

Originally Posted by vchesar

By the way, I never knew that if you have a cell with a hyperlink (not using the HYPERLINK function), the F&R will not modify the URL that you see.

Let me clarify this, because that is not quite correct. In a cell with a hyperlink, the cell has content, which you see. If you type in a URL, Excel will automatically create a hyperlink in that cell with the same address as the URL you typed.

The text that is displayed is changed by Find & Replace without changing the underlying linked URL. If I type a thousand links that look like this:

http://www.excelforu.com/excel-2007-...ml#post2484257


and then notice my typo I can do a find & replace to change "foru" to "forum". All the displayed text will be nicely fixed but all the hyperlinks will be unchanged. To do a F&R on hyperlinks requires VBA (AFAIK).

See next comment for detail.

Originally Posted by vchesar

It is surprising the Excel Hyperlink does not have the editing option. Or am I missing something.

I think you are missing something. In Excel you edit a hyperlink by right-clicking on the hyperlinked cell and then selecting Edit Hyperlink. It's the same as Word.

I still don't understand how using Word as an intermediate step is a workaround for your problem in Excel but I will try to take a look at your last couple of posts more closely as time permits.