Skip to main content

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!

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

                )

        )

    )

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.

Reply


Terms & Conditions