Extract particular data (Hyperlink) from Cell

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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
CDEFGHIJK
3SendereMail TextCustomer NameTel. NumberCountryRegionLast Known ChannelProductSurvey Link
4More 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 PhoneDT1321/GThttps://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
RangeFormula
E4E4=MID([@[eMail Text]],FIND("Name:",[@[eMail Text]])+6,FIND("Tel. Number:",[@[eMail Text]])-FIND("Name:",[@[eMail Text]])-6)
F4F4=MID([@[eMail Text]],FIND("Tel. Number:",[@[eMail Text]])+13,FIND("Country:",[@[eMail Text]])-FIND("Tel. Number:",[@[eMail Text]])-13)
G4G4=MID([@[eMail Text]],FIND("Country:",[@[eMail Text]])+9,FIND("Region:",[@[eMail Text]])-FIND("Country:",[@[eMail Text]])-9)
H4H4=MID([@[eMail Text]],FIND("Region:",[@[eMail Text]])+8,FIND("Last Known Channel:",[@[eMail Text]])-FIND("Region:",[@[eMail Text]])-8)
I4I4=CLEAN(TRIM(MID([@[eMail Text]],FIND("Last Known Channel:",[@[eMail Text]])+20,FIND("SR Number:",[@[eMail Text]])-FIND("Last Known Channel:",[@[eMail Text]])-20)))
J4J4=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))),".","")
K4K4=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]]))))
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,930
Members
452,367
Latest member
TePunaBloke

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