Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|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

1 Answer 1

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?

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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