Extract particular data (Hyperlink) from Cell

[sanket_sk]

Hi All,

Have some interesting query.



I have extracted the Email in the Excel cell, I have successfully extracted some details in the subsequent cells using the mid function.



There are multiple hyperlinks available in the cell since the hyperlink text length is dynamic I am facing challenge in extracting hyperlink which is located before the text “We ensure that your feedback is handled confidentially."



Sharing here one sample data, could you please help with formula / VBA to extract this hyperlink in Excel sheet Cell “K4” the requirement is to extract the exact hyperlink so that we can click/copy from excel and paste it to the browser to access the same.

Data Collection.xlsb
	C	D	E	F	G	H	I	J	K
3	Sender	eMail Text	Customer Name	Tel. Number	Country	Region	Last Known Channel	Product	Survey Link
4	More Satish	<https://s3.Panasonicaws.com/Ciscolia /Panasonic%20HealthCare%20Pulse/Brand_Logo/CC%26PPOS.PNG> Customer Contact Details: Name: Rajesh Bhatnagar Tel. Number: 919820098200 Country: India Region: Indian Subcontinent Last Known Channel: Phone SR Number: 10-0062455985 Panasonic/Non-Panasonic: Panasonic BrandLicenseeName: Licensee unknown Journey Type: IW R&E Dear Customer, Thank you for contacting Panasonic for support on your product DT1321/GT. We would appreciate your feedback about this recent interaction with our team. This will help us improve the quality of our service. Could you please answer 3 to 4 questions via following link (it will take you about 1 minute): How did you feel about your recent service experience? <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=1> Irritated <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=1> <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=2> Disappointed <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=2> <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=3> Neutral <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=3> <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=4> Happy <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=4> <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=5> Delighted <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&_score=5> https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9 We ensure that your feedback is handled confidentially. Thank you for your help. With kind regards, Achhut Godbole Seeger Global Head of supply chain This survey is being conducted on behalf of Panasonic by independent research agency Ciscolia and thus guarantees your privacy. As individual responses will be shared with Panasonic, who is the data controller for this survey, we invite you to read our Panasonic Privacy Notice <http://www.Panasonic.com/a-w/privacy-notice.html> to learn how we protect your privacy. <https://us.cdn.survey.Ciscolia .com/bdc9146c788e855ed859c6ae450667071bfcf850> <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&invite-opened=yes>	Rajesh Bhatnagar	919820098200	India	Indian Subcontinent	Phone	DT1321/GT	https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9We ensure that your feedback is handled confidentially.Thank you for your help.With kind regards,Achhut Godbole SeegerGlobal Head of supply chain This survey is being conducted on behalf of Panasonic by independent research agency Ciscolia and thus guarantees your privacy. As individual responses will be shared with Panasonic, who is the data controller for this survey, we invite you to read our Panasonic Privacy Notice <http://www.Panasonic.com/a-w/privacy-notice.html> to learn how we protect your privacy. <https://us.cdn.survey.Ciscolia .com/bdc9146c788e855ed859c6ae450667071bfcf850> <https://survey.Ciscolia .com/?edrrynnz5r837xwytwb9&invite-opened=yes>
NPS Data Collection

Cell Formulas
Range	Formula
E4	E4	=MID([@[eMail Text]],FIND("Name:",[@[eMail Text]])+6,FIND("Tel. Number:",[@[eMail Text]])-FIND("Name:",[@[eMail Text]])-6)
F4	F4	=MID([@[eMail Text]],FIND("Tel. Number:",[@[eMail Text]])+13,FIND("Country:",[@[eMail Text]])-FIND("Tel. Number:",[@[eMail Text]])-13)
G4	G4	=MID([@[eMail Text]],FIND("Country:",[@[eMail Text]])+9,FIND("Region:",[@[eMail Text]])-FIND("Country:",[@[eMail Text]])-9)
H4	H4	=MID([@[eMail Text]],FIND("Region:",[@[eMail Text]])+8,FIND("Last Known Channel:",[@[eMail Text]])-FIND("Region:",[@[eMail Text]])-8)
I4	I4	=CLEAN(TRIM(MID([@[eMail Text]],FIND("Last Known Channel:",[@[eMail Text]])+20,FIND("SR Number:",[@[eMail Text]])-FIND("Last Known Channel:",[@[eMail Text]])-20)))
J4	J4	=SUBSTITUTE(TRIM(CLEAN(MID([@[eMail Text]],FIND("support on your product ",[@[eMail Text]])+23,FIND("We would appreciate your feedback",[@[eMail Text]])-FIND("support on your product ",[@[eMail Text]])-23))),".","")
K4	K4	=TRIM(CLEAN(MID([@[eMail Text]],FIND("We ensure that your feedback is handled confidentially.",[@[eMail Text]])-57,FIND("We ensure that your feedback is handled confidentially",[@[eMail Text]]))))

 

[severynm]

Since you're on 365 you should be able to make use of the Search and/or Find functions. They will each return the location of the first instance of the substring you want to look for. For example you could search for "<" and ">" and then use the Mid function based on those results. If you want to do that multiple times to pull all of the links out, keep track of the result for the last time ">" was found, and use that number as your starting location for the next search.