Re: splitting content in long form text in a formula
Accepted Solutions
As long as your heading is always on a single line (not spanning multiple lines using line breaks), then this will do the trick.
First formula field: "Heading"
IF(Recipe, REGEX_EXTRACT(Recipe, "[^\n]*"))
Second formula field: "Recipe Body"
IF(Recipe, TRIM(SUBSTITUTE(Recipe, Heading, "")))
As long as your heading is always on a single line (not spanning multiple lines using line breaks), then this will do the trick.
First formula field: "Heading"
IF(Recipe, REGEX_EXTRACT(Recipe, "[^\n]*"))
Second formula field: "Recipe Body"
IF(Recipe, TRIM(SUBSTITUTE(Recipe, Heading, "")))
Is it possible to make the same thing on different columns
I'd like to put a line in a different column each time
@CSM_PF Is this what you're looking for?
If so, that's pretty easy to set up. First add a formula field to count the number of line breaks in the source text (I'll explain the purpose behind this later):
LEN(Input) - LEN(SUBSTITUTE(Input, "\n", ""))I named this field "Input Count", but feel free to name it whatever you want as long as the later formula fields reference it correctly.
The first extraction formula (Output 1) is the easiest:
IF(
Input,
REGEX_EXTRACT(Input, "[^\n]*")
)This basically gets everything up to—but not including—the first line break, but only if the field isn't empty. If the input is empty, this field will also be empty.
From that point on, we're going to use the previous extracted text to find the next piece to extract following a consistent pattern.
Output 2 looks like this:
IF(
{Item Count} > 0,
REGEX_EXTRACT(Input, "(?:" & {Output 1} & "\n)([^\n]*)")
)This basically says that if we've got at least one line break, find—but ignore—the first extracted text and the line break following it, then extract everything up to—but not including—the next line break.
Output 3 does the same thing, but increasing the line break count check and referencing Output 2:
IF(
{Item Count} > 1,
REGEX_EXTRACT(Input, "(?:" & {Output 2} & "\n)([^\n]*)")
)Repeat this pattern for as many fields as you need.
As for why we do the line count in the first place, this is the easiest way to prevent an error if the REGEX_EXTRACT() function can't find a given chunk. For example, this accounts for situations where one record might have 5 pieces to extract, and another only 3, and prevents those later fields from showing #ERROR in their output.
Okay, I found a way to make it work. It's not as clean as the first version, but it's accurate, which is more important.
The first two formulas for Output 1 and Output 2 stay the same. From that point on, you need to add all previous outputs and their separating newlines into the find-but-ignore part. Here are the formulas for Output 3 and Output 4 as examples:
IF(
{Item Count} > 1,
REGEX_EXTRACT(Input, "(?:" & {Output 1} & "\n" & {Output 2} & "\n)([^\n]*)")
)IF(
{Item Count} > 2,
REGEX_EXTRACT(Input, "(?:" & {Output 1} & "\n" & {Output 2} & "\n" & {Output 3} & "\n)([^\n]*)")
)As you can see, the formula will become longer with each successive field, but it gets the job done.
