2

I found several ways to get text between special characters. So far i manage well to do, but i have a problem i can't resolve. I need to get a string from the second time character to the next one. (I try to get an email address from a htmlbody field. I am using this formula inside a flow.

Example:

A nice Element1 text Element2 with info Element1 to read Element2 at home.

That we can interpret the following :

A text </td><td><a>with</a>info</td><td><a>to read</a>at home.

I need to get the text "to read".

I started from this :

MID(
    {!GetEmail.HtmlBody}, 
    FIND(
        '</td><td><a>',
        {!GetEmail.HtmlBody},
        FIND(
            '</td><td><a>',
            {!GetEmail.HtmlBody}
        ) + 12
    ), 
    FIND(
        '<', 
        {!GetEmail.HtmlBody}
    ) - (
        FIND(
            '</td><td><a>', 
            {!GetEmail.HtmlBody}
        ) + 12
    )
)

Then I tried using a function reverse : null result. And I tried nested find, error also. Can you help me? Thank you.

2
  • 1
    Formulas are great for very basic things, but when you need to do something with just a little bit more complexity then using Apex starts to make sense. Is that a possibility for you, or are you required to write a formula for this?
    – Derek F
    Oct 27, 2023 at 13:07
  • @DerekF It's more about my knowlodge. Yes the best you be i can write an apex that i can trigger inside my flow but i am affraid I don't have enough knowledge.
    – Stuart66
    Oct 27, 2023 at 13:16

2 Answers 2

1

I hope I understand the exact challenge raised by your question.

The following suggestion is ultimately created via the field formula editor and only once tested in a Flow, but on the basis of an SObject field. The formula looks dismal and I invite everybody to come up with something better.

LEFT(RIGHT(RIGHT({!GetEmail.HtmlBody}, (LEN({!GetEmail.HtmlBody}) - FIND('</td><td><a>', {!GetEmail.HtmlBody}) - 11)), (LEN(RIGHT({!GetEmail.HtmlBody}, (LEN({!GetEmail.HtmlBody}) - FIND('</td><td><a>', {!GetEmail.HtmlBody}) - 11))) - FIND('</td><td><a>', RIGHT({!GetEmail.HtmlBody}, (LEN({!GetEmail.HtmlBody}) - FIND('</td><td><a>', {!GetEmail.HtmlBody}) - 11))) - 11)), FIND('</a>', RIGHT(RIGHT({!GetEmail.HtmlBody}, (LEN({!GetEmail.HtmlBody}) - FIND('</td><td><a>', {!GetEmail.HtmlBody}) - 11)), (LEN(RIGHT({!GetEmail.HtmlBody}, (LEN({!GetEmail.HtmlBody}) - FIND('</td><td><a>', {!GetEmail.HtmlBody}) - 11))) - FIND('</td><td><a>', RIGHT({!GetEmail.HtmlBody}, (LEN({!GetEmail.HtmlBody}) - FIND('</td><td><a>', {!GetEmail.HtmlBody}) - 11))) - 11))) - 1)

It's on purpose, I've not added any line breaks and indentations. It wouldn't significantly improve readability. For an understanding it's more important to get the idea of the way the formula is generated.

Derek is right: From a certain point on, Apex is the better choice. Because formulas can't hold intermediate results in memory (do not have variables) and don't support regular expressions, the developer of a formula like this tends to do a lot of search-and-replace on parts of prior stages of the formula, outside of Salesforce's formula editors. This is what I've done, and I'm sure there is room for improvement.

We all know this result is hardly maintainable. I imagine getting a small change of the requirement, e.g. the second string to find (of the three) is not the same </td><td><a> as the first one, but changes to </td><td><div>. To replace the right fields will get tricky.

Here, on the other hand, is the Apex, if you take this route:

public class StringReplacer {
    @InvocableMethod
    public static List<String> extractStrings(List<String> strings) {
        List<String> results = new List<String>();
        for(String s : strings) {
            String result = s.replaceAll('\\n', '')
                .replaceAll('(.+?)</td><td><a>(.+?)</td><td><a>|</a>.*', '');
            results.add(result);
        }
        return results;
    }
}

Even if this looks cryptic for the uninitiated, for someone familiar with both Salesforce formulas and regular expressions, the latter is much simpler to comprehend and manipulate than the formula. (The first replaceAll removes all the line breaks. The second replaceAll removes as little as possible up to </td><td><a>, then again as little as possible up to a second </td><td><a>. It also removes everything from </a> till the end.)

The Test class clarifies, how I understood your requirement:

@IsTest
private class StringReplacerTest {
    @IsTest
    private static void extractStrings() {
        List<String> strings = new List<String>{
            'A text </td><td><a>with</a>info</td><td><a>to read</a>at home.'
        };

        Test.startTest();
        List<String> results = StringReplacer.extractStrings(strings);
        Test.stopTest();

        Assert.areEqual('to read', results[0]);
    }
}
0

I think you were on the right track, using MID() and nested FIND() to get the offsets and length.

Just for other people's reference, the magic number 12 here is because

  • FIND() returns the position of the first character of the search text
  • If you want to use nested FIND() to find the next instance of the same substring, you need to tell it to start at least 1 character after the previous instance
  • </td><td><a> is 12 characters long, so we're telling FIND() to start after the entirety of the previous instance

Your formula is actually fairly close to correct. The main part that you made a mistake on is the third paramater to the MID() function (the number of characters to take).

FIND('<', {!GetEmail.HtmlBody}) should return "8" (the position of the first open angle brace in your string)
and FIND('</td><td><a>', {!GetEmail.HtmlBody}) + 12 wouldn't return anything less than 12

That second bit actually returns "19", but the point is that your formula partially evaluates to
MID(<string>, 8, <negative integer>).
It's the <negative integer> part that's the biggest issue. It's not a syntax error, but any non-positive number for the number of characters is going to result in an empty string.

Instead, what you should be doing here is:

  • Find the position of the </a> after the second instance of </td><td><a>
  • Subtract the position just after the second instance of </td><td><a>
MID(
    {!GetEmail.HtmlBody}, 
    FIND(
        '</td><td><a>',
        {!GetEmail.HtmlBody},
        FIND(
            '</td><td><a>',
            {!GetEmail.HtmlBody}
        ) + 12
    /* FIND() returns the position of the first character of the substring */
    /* So you also need to add the length of your substring to get the     */
    /*   correct starting position for MID()                               */
    ) + 12, 
    /* Find the position of the `</a>` after the second instance of </td><td><a> */
    FIND(
        '</a>',
        {!GetEmail.HtmlBody},
        FIND(
            '</td><td><a>',
            {!GetEmail.HtmlBody},
            FIND(
                '</td><td><a>',
                {!GetEmail.HtmlBody}
            ) + 12
        ) + 12
    ) - (
    /* Subtract the starting position of the substring we want to be our result */
    /*   (12 characters after the second </td><td><a>)                          */
        FIND(
            '</td><td><a>',
            {!GetEmail.HtmlBody},
            FIND(
                '</td><td><a>',
                {!GetEmail.HtmlBody}
            ) + 12
        ) + 12
    )
)

Yes, there is quite a bit of repetition in that formula, but since variables aren't a thing in formulas, repetition is just a fact of life.

The part where you find the position just after the second </td><td><a>

FIND(
    '</td><td><a>',
    {!GetEmail.HtmlBody},
    FIND(
        '</td><td><a>',
        {!GetEmail.HtmlBody}
    ) + 12
) + 12

Could be broken out into a separate formula to make the main formula look nicer, but I imagine that would make maintenance harder (have to update two formulas when you make a change ...and you also have to remember that you have two formulas to manage)

/* Yep, look at that repetition                                       */
/* This is less typing, but the compiled size will be slightly larger */
/*   than if it were just a single formula                            */
MID(
    {!GetEmail.HtmlBody}, 
    after_second_td_td_a_position__c,
    FIND(
        '</a>',
        {!GetEmail.HtmlBody},
        after_second_td_td_a_position__c
    ) - after_second_td_td_a_position__c
)

A gotcha

Now while those formulas are correct (tested in my own org using the Name field on an Account), I suspect this won't work for you.

That's because I'm certain that {!GetEmail.HtmlBody} is a LongTextArea field, and LongTextArea fields cannot be used in formulas. This is documented in Formula Field Limits and Restrictions

If that's the case, then the error you should be running into is

Error: You referenced an unsupported field type called "Long Text Area" using the following field

If you want to work with long text fields, you have little choice but to handle that in Apex. Felix's answer should give you a head start on making a class with an @invocableMethod annotation (which is what you need to be able to use Apex code in a flow).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .