Help

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

Solved
Jump to Solution
4087 14
cancel
Showing results for 
Search instead for 
Did you mean: 
AnnicaT
7 - App Architect
7 - App Architect

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.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Here’s what I came up with:

Screen Shot 2019-12-30 at 5.09.02 PM

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.

See Solution in Thread

14 Replies 14

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.

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

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.

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
18 - Pluto
18 - Pluto

Here’s what I came up with:

Screen Shot 2019-12-30 at 5.09.02 PM

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()?

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!

AnnicaT
7 - App Architect
7 - App Architect

@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), "[ ] ", "")

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