up vote 1 down vote favorite
share [g+] share [fb]

The links appear hyperlinked with blue line in Excel, I want to extract the text from the link e.g. The url http:\sea.craigslist.com appears as "seattle" in excel. I want to extract the text "sea" out of the link and paste in a next column.

link|improve this question
could you clarify? you have a cell containing (blue-underlined) a link with TEXT="seattle" and LINK="sea.craigslist.com";, right? you would like to extract "sea" from the TEXT or the LINK? – wilson Sep 20 '10 at 7:40
feedback

1 Answer

up vote 0 down vote

This formula will pull out all text after the http:// and before the first period, where A1 is the cell containing the hyperlink. It's not an overly advanced method, but if all your data is in the same, simple, format this may be sufficent.

=MID( A1 , SEARCH("http://",A1)+7 , SEARCH(".",A1)-SEARCH("http://",A1)-7 )

However, this relies on the hyperlink URL being the visible text, and doesn't work when they are showing thier friendly name. In otherwords it doesn't work when the hyperlink is showing as just "Seattle", because the formula works on that instead of the URL.

So far, I cannot find a way around this issue without resorting to a custom VBA function - in which case the entire thing may as well be a custom function. Would this be an acceptable solution?

Alternatively, does anyone know of a built-in function to extract the link's target?

link|improve this answer
it could be done by defining a function in VBA (see link) – wilson Sep 20 '10 at 7:53
@Wilson, quite, as per my 3rd paragraph. And it's a very simple function indeed, even if you built it to also extract the subdomain as well. But people often seem to dislike using custom functions, hence my hesitation in offering it as the main thrust of my answer. – DMA57361♦ Sep 20 '10 at 9:19
actually i dislike custom function as well. :) but i just found that reference and share it here. seems that there is no equivalent built-in function. – wilson Sep 20 '10 at 10:41
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.