Help

Re: Split Formula

Solved
Jump to Solution
245 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Nicole_Merwin
4 - Data Explorer
4 - Data Explorer

Hello,

I have a rollup field with the following output:

FirstName1  Last Name1- XXXX Title Company , First Name2 Last Name2 - XX Title Company

Without going back to the original table to pull the "XXX Title Company"  I need a formula to help split and display everything after " - " for each value.

 

Any help is appreciated!

1 Solution

Accepted Solutions

Hey @Nicole_Merwin

Out of curiosity, is this along the lines of the behavior you're looking for?

Ben_Young1_0-1674871003683.png

Ben_Young1_1-1674871035801.png

Here's the formula in the Extract formula field on the right of the last screenshot:

 

IF(
    {Records},
    IF(
        REGEX_MATCH(
            {Records},
            "[,-]"
        ),
        REGEX_REPLACE(
            REGEX_REPLACE(
                {Records},
                ",\s",
                " \n"
            ),
            "\b.*-\s",
            ""
        )
    )
)

 

Truthfully, I'm not a fan of this formula at all, but here's how it works:
We start by searching the string value of what's displayed in the linked record field for a comma or a hyphen.
If the search returns a match, then we replace every comma with a new line operator (\n) so that each extracted value is displayed cleanly on a new line.
From there, we extract the returned string without any of the preceding characters before each hyphen on a new line.

If you just want to retain the comma separated values, you can use this version of the formula:

IF(
    {Records},
    IF(
        REGEX_MATCH(
            {Records},
            "[,-]"
        ),
        REGEX_REPLACE(
            {Records},
            "\\b(\w|\s)+-\\s",
            ""
        )
    )
)

Here's what it looks like in Airtable:

Ben_Young1_0-1674875705361.png

If you have a list of more specific requirements for how you'd ideally want the values formatted, please let me know and I'll look at tweaking it to fit your requirements.
Additionally, it's totally possible that I've just completely misunderstood what you're trying to accomplish, so please correct me if I'm not getting something from your original post.

Something I forgot to mention... in my examples, I was referencing the direct value of the record's linked record field instead of a rollup.
You should be able to just point the formula to the value of the rollup field. The reason I just pointed straight to the linked record field is just because I'm a bit of a hawk about data bloat in the form of unnecessary fields.

See Solution in Thread

2 Replies 2

Hey @Nicole_Merwin

Out of curiosity, is this along the lines of the behavior you're looking for?

Ben_Young1_0-1674871003683.png

Ben_Young1_1-1674871035801.png

Here's the formula in the Extract formula field on the right of the last screenshot:

 

IF(
    {Records},
    IF(
        REGEX_MATCH(
            {Records},
            "[,-]"
        ),
        REGEX_REPLACE(
            REGEX_REPLACE(
                {Records},
                ",\s",
                " \n"
            ),
            "\b.*-\s",
            ""
        )
    )
)

 

Truthfully, I'm not a fan of this formula at all, but here's how it works:
We start by searching the string value of what's displayed in the linked record field for a comma or a hyphen.
If the search returns a match, then we replace every comma with a new line operator (\n) so that each extracted value is displayed cleanly on a new line.
From there, we extract the returned string without any of the preceding characters before each hyphen on a new line.

If you just want to retain the comma separated values, you can use this version of the formula:

IF(
    {Records},
    IF(
        REGEX_MATCH(
            {Records},
            "[,-]"
        ),
        REGEX_REPLACE(
            {Records},
            "\\b(\w|\s)+-\\s",
            ""
        )
    )
)

Here's what it looks like in Airtable:

Ben_Young1_0-1674875705361.png

If you have a list of more specific requirements for how you'd ideally want the values formatted, please let me know and I'll look at tweaking it to fit your requirements.
Additionally, it's totally possible that I've just completely misunderstood what you're trying to accomplish, so please correct me if I'm not getting something from your original post.

Something I forgot to mention... in my examples, I was referencing the direct value of the record's linked record field instead of a rollup.
You should be able to just point the formula to the value of the rollup field. The reason I just pointed straight to the linked record field is just because I'm a bit of a hawk about data bloat in the form of unnecessary fields.

Ben this is amazing, and I can't explain how much I appreciate the breakdown. 

REGEX is its own language that I have not been able to wrap my head around. 

The formula worked perfectly. If you would like to tinker more, my only feedback is instead of comma seperate or a new line is to seperate each out put with an 'and' or 3+ outputs to be comma seperated and the last output being 'and'. 

Completely not necessary for my use case, but I could see this thread helping out others in the future and that may be of use!

Again, thank you so so much!