Skip to main content
Solved

How to separate out text into two fields based on a specific word?

  • December 6, 2022
  • 3 replies
  • 48 views
N
Forum|alt.badge.img+2

Hi there, 

I'm new to Airtable and trying to figure out how everything works. Let's say I have a cell that as the following information I inserted below: 


Ingredients

- 4 salmon fillets
- 2 tablespoons of melted butter
- 2 tablespoons of olive oil
- 4 cloves of minced garlic
- 2 tablespoons of freshly-chopped parsley
- Salt and pepper
- Lemon wedges (optional)

Instructions

1. Preheat the oven to 375 degrees Fahrenheit.

2. Place the salmon fillets onto a baking sheet lined with foil.

3. In a small bowl, mix together the melted butter, olive oil, minced garlic, and parsley.

4. Brush the butter mixture onto the salmon fillets.

5. Sprinkle salt and pepper over the fillets.

6. Bake the salmon for 20 minutes, or until the fish is cooked through.

7. Serve with lemon wedges, if desired. Enjoy!

 

How can I separate all of the "ingredients" into one cell? And all of the instructions into another? 

 

Best answer by Cameron_Kaiser

If Airtable supported Array Indexing, you could something like Split("Instructions")[0] and Split("Instructions)[1]. Since they don't, we have to get a lil crafty. Let's assume your main cell is called "Details".

For the Ingredients, we can find the index of "Instructions" and grab all content before that, by doing

Left(Details, Find("Ingredients"))

Since the cell you're putting that in will probably already be called "Ingredients", we can probably get rid of the "Ingredients" line as well. Formatting is different in the Airtable forum than in your cell, but here it looks like "Ingredients\n\n", the \n's being "linebreak" characters. So you could do this to remove that line:

SUBSTITUTE(LEFT(Details, FIND("Instructions", Details)), "Ingredients\n\n", "")

Now for the latter half, we can do basically the same thing.

SUBSTITUTE(MID(Details, FIND("Instructions", Details), 9999), "Instructions\n\n", "")

This tells the system to grab everything from "Instructions" and on, and also replaces the instructions line with nothing so you get just the good stuff. In the "Ingredients" field you'll find that there are two linebreaks at the end("\n\n"). You could remove this by just finding the index of that instead of Instructions alone, like FIND("\n\nInstructions", Details).

Here's what it looks like:

 

    3 replies

    C
    Forum|alt.badge.img+9
    • Cameron_Kaiser
    • Inspiring
    • Answer
    • December 6, 2022

    If Airtable supported Array Indexing, you could something like Split("Instructions")[0] and Split("Instructions)[1]. Since they don't, we have to get a lil crafty. Let's assume your main cell is called "Details".

    For the Ingredients, we can find the index of "Instructions" and grab all content before that, by doing

    Left(Details, Find("Ingredients"))

    Since the cell you're putting that in will probably already be called "Ingredients", we can probably get rid of the "Ingredients" line as well. Formatting is different in the Airtable forum than in your cell, but here it looks like "Ingredients\n\n", the \n's being "linebreak" characters. So you could do this to remove that line:

    SUBSTITUTE(LEFT(Details, FIND("Instructions", Details)), "Ingredients\n\n", "")

    Now for the latter half, we can do basically the same thing.

    SUBSTITUTE(MID(Details, FIND("Instructions", Details), 9999), "Instructions\n\n", "")

    This tells the system to grab everything from "Instructions" and on, and also replaces the instructions line with nothing so you get just the good stuff. In the "Ingredients" field you'll find that there are two linebreaks at the end("\n\n"). You could remove this by just finding the index of that instead of Instructions alone, like FIND("\n\nInstructions", Details).

    Here's what it looks like:

     

    TheTimeSavingCo
    N
    N
    Forum|alt.badge.img+2
    • NoCodeLearner32
    • Author
    • Participating Frequently
    • December 22, 2022

    Wow, this is a beautiful solution! Thank you for this. 

    One extra question, there are a few paragraph spaces that come in for some reason before the "ingredients" tab. Is there way to also in the same formula eliminate any spaces or extra text that comes in BEFORE Ingredients?

    N
    Forum|alt.badge.img+2
    • NoCodeLearner32
    • Author
    • Participating Frequently
    • December 22, 2022

    Sorry, or just add a little more context and change my question:

    Sometimes I get a text in the cell that looks like this: 
    Spaghetti with Soy Sauce and Egg Ingredients: - 2 servings of spaghetti - 2 eggs - 1 medium tomato, diced - 1 small onion, diced - 2 tablespoons of soy sauce - Salt and pepper to taste Instructions: 1. Boil the spaghetti according to package instructions. 2. Meanwhile, heat a large skillet over medium heat and add the onion. Cook until softened, stirring frequently, about 4 minutes. 3. Add the diced tomato and cook for an additional 1-2 minutes. 4. Crack the eggs into the pan and scramble until cooked through. 5. Add the cooked spaghetti and soy sauce to the pan. Stir to combine. 6. Add salt and pepper to taste. 7. Serve hot. Enjoy!


    How do I separate out the recipe name which comes BEFORE "ingredients". @Cameron_Kaiser 

    Terms & ConditionsAccessibility statement