Hi everyone,
We're using an external system pulling up the LinkedIn links in the following format:
_HL_ENCODED_http://linkedin.com/in/profilelink_HL_linkedin.com/in/profilelink_HL__blank_HL_
The goal is to have a proper link out if, for example:
http://linkedin.com/in/profilelink
or
I've tried using different combinations of SUBSTITUTE and LEFT/RIGHT with FIND to no avail in a formula field or with a Process Builder, could you please advise?
6 answers
Bohdan,
I'll hold you to that! haha
Sure, I used the MID function on your text format.
MID(text, start_num, num_chars)
text = _HL_ENCODED_http://linkedin.com/in/profilelink_HL_linkedin.com/in/profilelink_HL__blank_HL_
start_num = 13
I chose 13, because "_HL_ENCODED_" is 12 characters and I would like to start exactly after that.
num_chars = FIND("_HL_",Text__c,2)-13
I used the FIND function to find "_HL_" text in your original text. It will index the first "_HL_" it sees after your specified starting index. My starting index is 2 so it will not find the first "_HL_", but rather the second "_HL_" in your text. If I did not specify 2, then it will give me "h" as my output because the first "_HL_" index is 1 and that's how many num_chars the MID function will give me. In this scenario the first "_HL_" after index 2 is the one after "profilelink" which has an index of 47. Since I am trimming 12 characters off the front and having 47 characters in my string, I need to trim another 12 characters off the right end. It will not always be 47 based off of what "profilelink" is of course.
I hope I explained this well.
Tony
