Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 20, 2022 08:05 PM
Hi there!
I’m losing my mind trying to figure out a formula for something I thought would be quite simple.
Basically I have one field into which we input a URL (which comes from our CRM). I want to extract a particular portion of this and then add it to text in another field. To explain:
The original field might have something like the below:
https://author-p4650.adobeaemcloud.com/editor.html/content/dd/au/en/the-project/**about-us/our-favou...
I’d like to extract just the section in bold and add it to the following:
https://www.danmurphys.com.au/the-project/
to create the below in the formula field:
https://www.danmurphys.com.au/the-project/about-us/our-favourite-restaurants-with-a-view
I’m not super well versed in formula writing but have tried quite a few things. While the first portion of the original string (ending with “the-project/”) is always that exact number of characters (80 in this example), the issue I keep having is that the portion I want to extract isn’t a specific number of characters (it’s always different). And I also don’t want to include the “.html” portion at the end.
Can anyone help me figure out:
A. How to extract the exact portion of text that I want?
B. How to add it to the end of the new string?
C. How to do all this with one formula field?
Thanks SO much!
– Lara
Solved! Go to Solution.
Jul 20, 2022 09:20 PM
Hi Lara, try this formula:
SUBSTITUTE(
"https://www.danmurphys.com.au/" &
RIGHT(
{URL},
LEN({URL})
-
FIND(
"/the-project",
{URL}
)
),
'.html',
''
)
Jul 20, 2022 09:20 PM
Hi Lara, try this formula:
SUBSTITUTE(
"https://www.danmurphys.com.au/" &
RIGHT(
{URL},
LEN({URL})
-
FIND(
"/the-project",
{URL}
)
),
'.html',
''
)
Jul 20, 2022 09:25 PM
Hey Adam!
Thanks so much for the super speedy response. That works perfectly!
I also managed to get my Dad on the phone for help and he helped me get to the following, which also works:
IF({AEM URL}=“”,“”,“https://www.danmurphys.com.au” & MID({URL},FIND(“/the-project”,{URL}),LEN({URL})-FIND(“/the-project”,{AEM URL})-4))
To be honest they both completely confuse me but just very glad to have the problem solved.
Thanks so much!