Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Taking part of string from a long text field based on number list

Topic Labels: Formulas
1728 3
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi all,

I’m not too familiar with Airtable formulas yet.

I have one long text field which has the following single record:

  1. This is a sentence
  2. This is also a sentence
  3. This is the final sentence

I would like to take the whole sentence from each line minus the 3 characters from the left (so taking away the “1”, the “.” and the blank space “”). These would be pasted into separate formula fields.

Formula field 1:
This is a sentence

Formula field 2:
This is also a sentence

Formula field 3:
This is the final sentence

I hope that makes sense, any help much appreciated!

3 Replies 3

I think you could use FIND() or SEARCH() functions to find the positions of number characters, and then extract sentences with MID().

Well, it would be easier if the sentences in your long date field ended in a period; in that case, you could do something like this:

IF(
    {LongText},
    MID(
        {LongText},
        FIND(
            '1. ',
            {LongText}
            )+3,
        (FIND(
            '.',
            {LongText},
            FIND(
                '1. ',
                {LongText}
                )+3
            )
            )-(FIND(
                '1. ',
                {LongText}
                )+3
                )+1
        )
    )

Otherwise, you’ll have to do something like this for the first and second sentences

IF(
    {LongText},
    MID(
        {LongText},
        FIND(
            '1. ',
            {LongText}
            )+3,
        (FIND(
            '2.',
            {LongText},
            FIND(
                '1. ',
                {LongText}
                )+3
            )
            )-(FIND(
                '1. ',
                {LongText}
                )+3
                )-1
        )
    )

and something like this for the third

IF(
    {LongText},
    MID(
        {LongText}&'|',
        FIND(
            '3. ',
            {LongText}
            )+3,
        (FIND(
            '|',
            {LongText}&'|',
            FIND(
                '3. ',
                {LongText}
                )+3
            )
            )-(FIND(
                '3. ',
                {LongText}
                )+3
                )
        )
    )

This worked a treat! Thanks @W_Vann_Hall.