Skip to main content

Is there any way to use the FIND function to search a field backwards (in reverse order)?


In other words, I want to find the LAST occurrence of a certain letter/symbol in a field.


So, let’s say I had this text in a field:

cats_and_dogs_in_playground_part2_lg600125X7aspUT


I want to find the position number of the FINAL underscore character so I could then use the LEFT or RIGHT functions to strip out all of the text before or after the position number of the final underscore character.


The problem is that all of the fields will have a varying number of underscores, and all of the fields will have a varying number of characters both before & after the final underscore


If the FIND formula enabled us to search BACKWARDS in a field, I could simply tell it to stop at the very first underscore character that it finds when searching in reverse.


Or — if Airtable gave me some way to COUNT the number of underscores within the field (which would result in 6, based on the example above), and then if there was some way for me to tell it to FIND the position number of the 6th underscore (instead of the 1st matching underscore), then I would have the position number that I need.


I’m curious if there’s even a way to figure out this puzzle. I’m assuming that this would need to be done with Javascript using the Scripting Block.

Hi @ScottWorld - yes, scripting block is the way to go here. Javascript offers a “last index of” method, so you could use this in a script to populate another field with the part of the original string you want:


// set the table and query

let table = base.getTable("Table 1");

let results = await table.selectRecordsAsync();



// for each record in the result set

for (let record of results.records) {

    // get the input string

    let str = record.getCellValue('String');

    // get the index of the last occurrence of an underscore

    let n = str.lastIndexOf('_');

    // get the last segment base on the last index

    let lastPieceOfString = str.substring(n+1, str.length)

    // update the table with the lastPieceOfString

    await table.updateRecordAsync(record, {

        "Last Part of String": lastPieceOfString

    })

}


JB

Hi @ScottWorld - yes, scripting block is the way to go here. Javascript offers a “last index of” method, so you could use this in a script to populate another field with the part of the original string you want:


// set the table and query

let table = base.getTable("Table 1");

let results = await table.selectRecordsAsync();



// for each record in the result set

for (let record of results.records) {

    // get the input string

    let str = record.getCellValue('String');

    // get the index of the last occurrence of an underscore

    let n = str.lastIndexOf('_');

    // get the last segment base on the last index

    let lastPieceOfString = str.substring(n+1, str.length)

    // update the table with the lastPieceOfString

    await table.updateRecordAsync(record, {

        "Last Part of String": lastPieceOfString

    })

}


JB


Oh wow! That’s amazing!! I love that JavaScript has “last index of” functionality built right into it!


Thank you so much for typing up that script for me! You are the best!! :grinning_face_with_big_eyes: 🙌 :thumbs_up:


I’ve also realized that I will likely need to learn Javascript. 😝

Oh wow! That’s amazing!! I love that JavaScript has “last index of” functionality built right into it!


Thank you so much for typing up that script for me! You are the best!! :grinning_face_with_big_eyes: 🙌 :thumbs_up:


I’ve also realized that I will likely need to learn Javascript. 😝



Learning JavaScript will benefit you in many more ways than just building Airtable Scripts. JavaScript is the ubiquitous language of the web and web-based tools. Anyone who has the opportunity to learn JavaScript should most definitely take that opportunity 🙂


Learning JavaScript will benefit you in many more ways than just building Airtable Scripts. JavaScript is the ubiquitous language of the web and web-based tools. Anyone who has the opportunity to learn JavaScript should most definitely take that opportunity 🙂



Thank you! 🙂 Totally agree! I started taking Codecademy’s “Intro to Javascript” course last week, and it has been very enlightening so far. I also ordered 2 Javascript books as well. Let’s see how far I can go with it! 🙂


Although I hope that Airtable doesn’t push all of their future “advanced functionality” into the Javascript scripting block — it would be nice for them to continue giving us more advanced functionality within the product itself, which would retain its ease-of-use for non-coders.

Reply


Terms & Conditions