how to extract a website address(not hyperlink) from within a text using a formula

3aFaReeT

New Member
Joined
Jul 8, 2019
Messages
2
I wonder if there is a way to extract a website address from within a text. noting that :
1- text field contains mix of email addresses and website address
2- email address can be before the website address or after it
3-some cells contain more than a mail address and a website address
4- there are no common separators between the email and website address. sometime "/" is used sometimes "-" and most of the times a space is a separator.

one way is using text to column separating columns by the above separators but it becomes a tedious approach as it requires multiple level of separation and filtering specially when data is very large.
another way could be using right, left or mid function but given the address is not always to right or left or even centre it is kind of difficult

i would appreciate if anyone can help
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
do all web addresses have www
 
Upvote 0
do all web addresses have www

no some have http and some just start as address.
but most of them have www

i figured out a formula that works mostly fine but fails if web address is on right of text and is not followed by space.
here is what i managed so far

=IFERROR(MID(C12,FIND("www",C12),(FIND(" ",C12,FIND("www",C12))-FIND("www",C12))),MID(C12,FIND("http",C12),(FIND(" ",C12,FIND("http",C12))-FIND("http",C12))))

Column C contains the text field
 
Upvote 0
have you got a selection of strings to work againgst
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,239
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top