Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How to extract a portion text from one field and add it to other text in another field

Topic Labels: Formulas
Solved
Jump to Solution
1821 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Lara_Chan-Baker
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hi Lara, try this formula:

SUBSTITUTE(
  "https://www.danmurphys.com.au/" & 
  RIGHT(
    {URL},
    LEN({URL}) 
    -
    FIND(
      "/the-project",
      {URL}
    )
  ),
  '.html',
  ''
)

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hi Lara, try this formula:

SUBSTITUTE(
  "https://www.danmurphys.com.au/" & 
  RIGHT(
    {URL},
    LEN({URL}) 
    -
    FIND(
      "/the-project",
      {URL}
    )
  ),
  '.html',
  ''
)

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!