splitting content in long form text in a formula

Worked like a total charm! thanks! 

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):

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:

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:

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:

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.

Literally seconds after posting the above solution, I realized that there's a slight problem with it. In your sample text, you have two lines that both contain "Test again". That's going to throw off the pattern matching when it comes to finding the second of those two lines, and all the lines that come after them. Let me see if I can quickly find a workaround (I'm short on time this morning).

Don't worry, there will never be equivalent lines normally. It was just an example!

I'll try your solution straight away!

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:

As you can see, the formula will become longer with each successive field, but it gets the job done.

Thanks @Justin_Barrett it works.

Could you tell me what is the good formula to separate the column A : Ben#///#test-bug#///#Feb 22, 2024 3:54pm UTC#///#Budget#///#Test again#///#Test again#///#Moderate in the following colums

Column B : Ben
Column C : test-bug
Column D : Feb 22, 2024 3:54pm UTC
Column E : Budget
Column F : Test again
Column G : Test again
Column H : Moderate

Thanks for your help

@CSM_PF The solution for that isn't much different than my originals above.

For the item count field, the formula changes to this. Because the separators are five characters long, the result needs to be divided by 5:

If you can guarantee that each line's content will be unique, you can use the original versions in my first reply. Here's the modified version of the formula for Output 1:

Output 2 now looks like this:

The rest of the pattern should be easy to figure out.

If you can't guarantee uniqueness among the different sections, then you'll have to use the second version with similar modifications.

@Justin_Barrett it works perfectly except for the last data field in my example - Column H: Moderate - do you know why?

This returns the same data as the previous column, i.e. "Test Again".

Found a solution with the following formula :

IF( Input, REGEX_EXTRACT(Input, "[^#]*$") )

@CSM_PF Glad that you found a solution!

As to why it repeated, that's likely because of the duplicate data as I said before. If you have two sections both with the same content—e.g. "Test Again"—the first version won't work. You'll need to use the second version because it progressively adds each previous section to the "to ignore" part of the regular expression. Your solution gets around that by specifically targeting what's at the end of the string after the final "#" symbol.