Skip to main content
Solved

Formula to find and Replace/Substitute entire line in Long Text field

  • December 29, 2019
  • 14 replies
  • 78 views
A
Forum|alt.badge.img+3

Hi!
I have a Long text field that has 1-52 lines.
Each line starts with either [ ] or [ x ] and is then followed by 10 digits (at present).

  1. How would I “magically” disapear all lines starting with [ x ] using a formula?

  2. Is there a way to "disapear " a whole line from [ x ] to \n without knowing the amount of caracters inbetween? That would be ideal if possible.

I need to “disapear” lines from top to bottom (first line 1, then line 2, then line 3 and so on…) and not at random through the entire field if that makes any difference.

I have tried various “solutions”, but haven’t found what I need. I’m probably nesting/combining it incorrectly and by now I’ve stared myself blind at the problem trying to get it right. I would greatly appreciate any and all help I could get.

Best answer by Justin_Barrett

Here’s what I came up with:

The formula in {Remaining} is:

MID(Original, FIND("[ ]", Original), 100000)

That final number is so large because it needs to capture whatever comes after that first unchecked item. If you find that it’s not grabbing everything, just make it bigger.

    14 replies

    B
    Forum|alt.badge.img+19
    • Bill_French
    • Inspiring
    • December 29, 2019

    Yes. Have you had a look at Zapier - I think you could use it to read the long text field, perform an iterative parsing using regular expressions, and put back (into Airtable in a new field) the resulting parsed version of the content.

    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 29, 2019

    Yes. Have you had a look at Zapier - I think you could use it to read the long text field, perform an iterative parsing using regular expressions, and put back (into Airtable in a new field) the resulting parsed version of the content.


    Unfortunately I haven’t tried Zapier more than getting an account on a whim and never getting around to using it. Do you have any hints to how to even go about doing it with Zapier? More than already mentioned?

    Is there any other ways using formula?

    I’m far from against using Zapier, I just feel like I’d need a way more detailed map to navigate doing that.

    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 30, 2019

    Yes. Have you had a look at Zapier - I think you could use it to read the long text field, perform an iterative parsing using regular expressions, and put back (into Airtable in a new field) the resulting parsed version of the content.


    @Bill.French
    I just had a look at Zapier and can only select to do things when there’s a new record or a new record in a view. So I can’t figure it out. Do you have any directions? Anyone else?

    B
    Forum|alt.badge.img+19
    • Bill_French
    • Inspiring
    • December 30, 2019

    Let’s revisit these requirements.

    You are attempting to process text within a field. This is not usual and customary field-level processing. Even in Google Sheets and Excel this would be complicated to do. I would have to write integrated code to achieve this in those platforms because it involves parsing and reorganizing text strings.

    To parse text you need a number of string-related functions not the least of which is Split(), a staple of string parsing and requested more than 5 years ago. As such, it’s extremely unlikely your requirements can ever be met in the near term.

    Perhaps others can weigh in on this topic, but it’s my assessment that the only way to magically add this functionality is with a custom API integration - a process that is always looking for specific text patterns in your data and transforming them into your desired formats.

    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 30, 2019

    Let’s revisit these requirements.

    You are attempting to process text within a field. This is not usual and customary field-level processing. Even in Google Sheets and Excel this would be complicated to do. I would have to write integrated code to achieve this in those platforms because it involves parsing and reorganizing text strings.

    To parse text you need a number of string-related functions not the least of which is Split(), a staple of string parsing and requested more than 5 years ago. As such, it’s extremely unlikely your requirements can ever be met in the near term.

    Perhaps others can weigh in on this topic, but it’s my assessment that the only way to magically add this functionality is with a custom API integration - a process that is always looking for specific text patterns in your data and transforming them into your desired formats.


    @Bill.French
    Thank you Bill for weighing in. It is greatly appreciated. I was half suspecting that it might not be doable, but was hoping that not finding a solution was due to my noob-ness at Airtable and not the challenge of the task.

    Justin_Barrett
    Forum|alt.badge.img+21

    To add a few more comments…

    In your original request, you essentially asked if it’s possible to modify a long text field via a formula. In short, it’s not. Formulas cannot modify other fields. They can only pull data from other fields to use in a formula. All formula field output appears in the formula field itself.

    With that in mind, and also noting your comment re: wanting to remove these checked off items from the top down, you could make a formula that would read the contents of your long text field, strip off the checked off bits, and output what’s left, but again, the result would stay in the formula field. In the end, you’d have your full list—including checked off items—in the long text field, and the same list minus the checked off items in the formula field. If that’s of any use to you, let us know and we can help work up the formula.

    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 31, 2019

    To add a few more comments…

    In your original request, you essentially asked if it’s possible to modify a long text field via a formula. In short, it’s not. Formulas cannot modify other fields. They can only pull data from other fields to use in a formula. All formula field output appears in the formula field itself.

    With that in mind, and also noting your comment re: wanting to remove these checked off items from the top down, you could make a formula that would read the contents of your long text field, strip off the checked off bits, and output what’s left, but again, the result would stay in the formula field. In the end, you’d have your full list—including checked off items—in the long text field, and the same list minus the checked off items in the formula field. If that’s of any use to you, let us know and we can help work up the formula.


    Hi @Justin_Barrett!
    That would be of extremely good use for me. That was pretty much how I had figured it would be done if I managed to figure out how to do it and was what I was atempting, and failing, to do on my own.

    Justin_Barrett
    Forum|alt.badge.img+21
    • Justin_Barrett
    • Inspiring
    • Answer
    • December 31, 2019

    Here’s what I came up with:

    The formula in {Remaining} is:

    MID(Original, FIND("[ ]", Original), 100000)

    That final number is so large because it needs to capture whatever comes after that first unchecked item. If you find that it’s not grabbing everything, just make it bigger.

    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 31, 2019

    Here’s what I came up with:

    The formula in {Remaining} is:

    MID(Original, FIND("[ ]", Original), 100000)

    That final number is so large because it needs to capture whatever comes after that first unchecked item. If you find that it’s not grabbing everything, just make it bigger.


    You, sir, are an absolute star! :star: :star: :star: :star: :star:
    That’s amazing. Works lika a charm.

    As a curiosity, is it possible, if the text in the Long text column are dates (one per line) to in the formula column show only the dates that are after TODAY()?

    Justin_Barrett
    Forum|alt.badge.img+21

    You, sir, are an absolute star! :star: :star: :star: :star: :star:
    That’s amazing. Works lika a charm.

    As a curiosity, is it possible, if the text in the Long text column are dates (one per line) to in the formula column show only the dates that are after TODAY()?


    Unfortunately not. That would require the ability to parse dates individually from within a full list of dates, which Airtable cannot do.

    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 31, 2019

    Unfortunately not. That would require the ability to parse dates individually from within a full list of dates, which Airtable cannot do.


    Thank you for answering. I figured it would have troubles doing that, which is why I went the other route from the start. It’s nice though to have it confirmed that my hunch and grasp of things, even in my noob-dom, were’nt that far off or even off at all.

    Thank you SOOO much for your help! Truly, thank you!

    Justin_Barrett
    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 31, 2019

    @Justin_Barrett

    Is there a way to in the {Remining} substitute the [ ] so you get the text sans the brackets?
    I’ve been trying and can get it to accept the code, but it isn’t working as I intended (removing the actual brackets) which tells me that I’m most likely nesting it incorrectly.

    Justin_Barrett
    Forum|alt.badge.img+21

    @Justin_Barrett

    Is there a way to in the {Remining} substitute the [ ] so you get the text sans the brackets?
    I’ve been trying and can get it to accept the code, but it isn’t working as I intended (removing the actual brackets) which tells me that I’m most likely nesting it incorrectly.


    Wrap a SUBSTITUTE() function around the formula above.

    SUBSTITUTE(MID(Original, FIND("[ ]", Original), 100000), "[ ] ", "")
    A
    Forum|alt.badge.img+3
    • AnnicaT
    • Author
    • Known Participant
    • December 31, 2019

    Wrap a SUBSTITUTE() function around the formula above.

    SUBSTITUTE(MID(Original, FIND("[ ]", Original), 100000), "[ ] ", "")

    @Justin_Barrett
    That explains it. I tried wrapping it the other way, SUBSTITUTE on the inside. sigh

    Thank you so much for all your help!

    Happy New Year! May fortune bless you in the new year and decade. :champagne: :clinking_glasses:

    Justin_Barrett
    J
    Terms & ConditionsAccessibility statement