Skip to main content

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

  • February 28, 2019
  • 3 replies
  • 40 views
T
Forum|alt.badge.img+2

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

E
Forum|alt.badge.img+17

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

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • February 28, 2019

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
                )
        )
    )
T
T
Forum|alt.badge.img+2
  • Tom_R
  • Author
  • Inspiring
  • March 6, 2019

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.

Terms & ConditionsAccessibility statement