Dec 29, 2019 03:07 AM
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).
How would I “magically” disapear all lines starting with [ x ] using a formula?
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.
Solved! Go to Solution.
Dec 30, 2019 05:12 PM
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.
Dec 29, 2019 08:01 AM
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.
Dec 29, 2019 08:48 AM
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.
Dec 30, 2019 01:55 PM
@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?
Dec 30, 2019 03:09 PM
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.
Dec 30, 2019 03:39 PM
@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.
Dec 30, 2019 04:11 PM
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.
Dec 30, 2019 04:19 PM
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.
Dec 30, 2019 05:12 PM
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.
Dec 30, 2019 05:22 PM
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()?