Formula to Extract URL & Text from Href Tag

I figured out a workaround in Google Sheets but I can't figure it out in Excel.

<a href="http://www.smartinsights.com/mobile-marketing/mobile-marketing-analytics/mobile-marketing-statistics/">new report</a>

I'd like a way to separate the URL and link text from above into 2 columns

A B
http://www.smartinsights.com/mobile-...ng-statistics/ new report

Thanks for any suggestions.

Both solutions worked! Thanks so much for your help.

For some reason, I can't access Edit to mark this post as Solved

little modyfication

Formula:

Please Login or Register  to view this content.

and drag accros

Go to the first post, from Thread Tools select Mark as Solved

should be (COLUMN($A$10)+2)

btw. I can't edit post

Thanks sandy666.

That modified formula does work,but there is a problem. It starts at cell 10 instead of cell 2 (first cell is a column header)

If your URL and name is in B column
=IF(COLUMN(C10)=(COLUMN($B$10)+2),MID($B$10,FIND(">",$B$10)+1,FIND("</a>",$B$10)-FIND(">",$B$10)-1),MID($B$10,FIND("http",$B$10),FIND(">",$B$10)-FIND("http",$B$10)-1))

simply change number of address to your needs

Sandy666,

One last thing, and I promise to leave you alone.

Is there a way for this to work on a relative path HREF (without http & domain)

Ex: <a href="/work/next-generation-lighting/">&amp;mdash;Jay Andrews at NextGen Lighting</a>

If source is in B2

Formula:

Please Login or Register  to view this content.

If source is in B2 and sources are mixed (http or without http) you can try

Formula:

Please Login or Register  to view this content.

but first check it and change to addresses suitable to your needs.

That's Perfect. Thanks sandy666!

Happy to help
If it's helped you add reputation star and mark thread as solved
Thank you.