splitting content in long form text in a formula

[jm22]

TIA - this community is amazing and so helpful when I can't get my head around these more tricky ones. but I am learning more and more! 

I have a bunch of method steps for our recipes:

COOK THE SALMON
Pat dry salmon with paper towel, remove the skin and cut into bite size pieces, discarding the skin. Add salmon, olives and courgette to the pot. Simmer, gently stirring occasionally, for 5-6mins, or until the salmon is cooked through and all the veggies are tender. Remove from heat. Check seasoning. "

MAKE THE AVOCADO DIP
Cut lemon into wedges.
Halve avocado, remove the stone and scoop out flesh with a spoon. Place avocado into a third medium bowl and mash with fork until smooth. Mix in aioli and (1/2 tbsp/1 tbsp) lemon juice. Season with salt, pepper and more lemon juice to taste. Set aside.

TIP: If you prefer, add avocado to the salad in STEP 5 and serve aioli on the side. "

MAKE THE HERBY VINAIGRETTE
Finely chop (half/whole) pack coriander, including the stalks. Add to small bowl with (1 tsp/2 tsp) honey, (2 tbsp/4 tbsp) olive oil and (1 tbsp/2 tbsp) white wine vinegar. Stir together until combined. Season to taste with salt and pepper. Set aside.

TIP: You may use your preferred sweetener instead of honey."

You can see they are all different. 

I want to have one column for the heading (in caps) and another column for the method directions. 

Do you think I'll have any joy with this one? 

thanks! 

[Justin_Barrett]

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

(LEN(Input) - LEN(SUBSTITUTE(Input, "#///#", ""))) / 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:

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

Output 2 now looks like this:

IF(
  {Item Count} > 0,
  REGEX_EXTRACT(Input, "(?:" & {Output 1} & "#///#)([^#]*)")
)

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.

[CSM_PF]

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

[CSM_PF]

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

[CSM_PF]

Found a solution with the following formula :

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

[Justin_Barrett]

@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.