Deleting Lines of Text Containing One Word

Hey ​@ceg_ would you mind sharing a couple of different examples on how this looks please? (the actual text for at least 2 examples).

We can most probably come up with a formula that will get rid of such line for you. But I’d like to see how the text is structured and what comes after Page. Also please explain what should be deleted from such examples.

Mike, Consultant @ Automatic Nation

Sure, the unedited records look like this (the trigger word is actually Highlight, I had my order incorrect):

Record A 

Highlight(blue) - Page 34 · Location 290

“Look at that. That thing’s almost a book.”

Highlight(yellow) - Page 34 · Location 292

“You think… she’s going to print it? When it’s done?”

Highlight(pink) - Page 34 · Location 294

“I hope so,” I say as brightly as I can manage. “She seemed interested in the idea and she… well, the march is coming up and…”

Record B

“Is this is an interrogation?”

Highlight(yellow) - Page 277 · Location 5335

“Is there something you need to be interrogated for?”

Highlight(pink) - Page 277 · Location 5337

“No.”

For each, I’d like to just delete the lines beginning with ‘Highlight’ so the records appear as such:

Record A 

“Look at that. That thing’s almost a book.”

“You think… she’s going to print it? When it’s done?”

“I hope so,” I say as brightly as I can manage. “She seemed interested in the idea and she… well, the march is coming up and…”

Record B

“Is this is an interrogation?”

“Is there something you need to be interrogated for?”

“No.”

Hi ​@ceg_ ,

I believe that a formula will not work on this since you line starts with highlight but is not always the same after that, and you have multiple of these lines. Substitute, replace or any of the REGEX functions will not work well here, unless you hardcode a massive formula, not practical though.

Your best bet is to use a script. You can use something like this script below and it will clean up all your records assuming this data are all on the same field. You can run this on a script extension and it will clean all your records, or you can modify it a bit and add it to an automation script so that it will do the clean up whenever you add new records for example. You can also make the keyword “Highlight” into a variable that you can change in case that changes in the future.

Hope this helps!

const TABLE_NAME = "Your Table Name";
const FIELD_NAME = "Your Field Name";

let table = base.getTable(TABLE_NAME);
let query = await table.selectRecordsAsync();

for (let record of query.records) {
  let text = record.getCellValueAsString(FIELD_NAME);
  if (!text) continue;

  // Split into lines, remove any that start with "Highlight", then re-join
  let cleaned = text
    .split("\n")
    .filter(line => !line.trim().startsWith("Highlight"))
    .join("\n");

  // Only write back if something changed
  if (cleaned !== text) {
    await table.updateRecordAsync(record.id, {
      [FIELD_NAME]: cleaned
    });
  }
}

Try this:

REGEX_REPLACE(
  {Notes},
  ".*Highlight.*\\n\n?",
  ""
)

​@TheTimeSavingCo thats a great solution! I would say that is more elegant and practical than what I shared. I didn’t think regex would work that well. Thanks for sharing! 
 

​@ceg_ definitely go with the formula version. The only time I would suggest the script is if you need the cleaned version to be an editable field that you would then make additional manual changes, or if you have a lot of these type of fields where creating a new formula field for each wouldn’t be practical. 

​@TheTimeSavingCo Sorry for the delay in responding-- I was on a deadline --but I tried this and it worked SWIMMINGLY! I cannot thank you enough, you probably just saved me 10-15 hours of work a week. 

Don’t forget about Airtable AI field. 

Here is a sample prompt:

remove blank lines and lines that start with "Highlight" from {field name}