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.
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]])))) |