Salesforce is really good at a lot of things, but digesting or looking for bits of data within large chunks of text is not a particular strong suite, though to be fair, not may systems can brag about how awesome it’s parsing ability is. I was thinking about this subject since there seems to be two camps, the configuration camp backing workflows and formulas and the code camp backing apex. I am going to propose a bit different way…an adminveloper way.

To start with, we are going to use flows for this. The reason I am going to use a flow is that I want this as object agnostic as possible and I don’t want to be constrained by any of the formulaic limitations (Looking at you char limit of related formulas!). Using a flow will also let me do some fun stuff later on (cough, next blog post, cough), but the biggest reason I want to use flows is the variables.

But, before we get into the nuts and bolts, I need to first talk about how I changed my thinking about this. Let’s say I have some questions in a text field and I want to parse out the answers to these questions. In the past, I might have tried to hard code in some right / left functions, but that is rigid and leads to stuff like “Answer this question in less than 255 char…”, ugh. Instead of trying to build out left / right stuff that ignores questions, you need to think of questions as keys in your document. They are a unique set of text / numbers that should only occur once in your doc, and because we are thinking like this, we can use the FIND function to locate their specific points within the text.

For testing purposes, I am just building out a basic flow with a long text input field that will be parsed.

The flow will look for my “keys” (AKA, questions) and determine how much distance (in char) between the LAST CHAR of a question and the FIRST CHAR of the next question. For the last question, we are going to use a footer as the marker (“Thanks for your time!”).

I am going to do this with a series of formulas, one for each question:

1. Find out how long the whole piece of text is (This is just a LEN(your text here), not going to expand it out.)
2. Find out where each question ends
3. Find out how long each answer is
4. Build out the answer

Formula number two is finding out where the questions ends. To do this, you use the “find” function, looking for the variable within your text. Now, you need to ADD to this the length of the question since you are looking for the END of the question and FIND returns the char location. Here is what the formula looks like:

find({!varQuestion1},{!Enter_Sample_Text_Here})+len({!varQuestion1})

Now that we know where the first question ends, we need to find out how long the answer is. We do this by subtracting the value from the first formula from where the value of where the next question (or text) starts. I did it this way, though you could have also just used a new find function. In a nutshell, at this point, we have determined that the end of question 1 is X and the start of question 2 is Y and that the answer is Y-X long.

({!FindQuestion2}-len({!varQuestion2}))-{!FindQuestion1}

Knowing this, we are going to go ahead and build out the formula that strips out the answer from the text. It is going to use a combination of left and right functions. We are essentially grabbing all text to the RIGHT of the end of question1 and then taking just LEFT text of that in the length of the Answer

left(
right({!Enter_Sample_Text_Here},{!GetLen}-{!FindQuestion1}),{!Question1Length}-1)

For the last question, you would just use the footer location:

{!FindQuestion4}-{!FindFooter}

FindFooter = find(“Thanks for your time!”,{!Enter_Sample_Text_Here})

This is how this function would look just running it from a flow:

Entered Data

Parsed Out

So yeah, with just a few formulas inside of a flow, parsing can be easy – peasy if you adjust your thinking. Now, before I leave you all hanging for part 2 of this blog post, let’s also take a look at something fun. What happens if someone starts to monkey with the questions? Well, through a little bippity boppity boo we can do something about it, without any more formulas. See we are using the FIND function already in the first formula that finds the end point of the question…and if the find function doesn’t get a match it returns a zero, thus, we know that if the formula that finds the end point of a question returns the length of the key variable something is off. This can be done with just a decision!

Alrighty, that is it for now! Part 2 is going to expand on this FUNctionalitye even more, so stay tuned!
As always, let me know if there are any questions / comments / or you just want to buy me coffee and pick my brain.