+4Hello,
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!
Best answer by Ben_Young1
Hey @Nicole_Merwin!
Out of curiosity, is this along the lines of the behavior you're looking for?
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:
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.
+22Hey @Nicole_Merwin!
Out of curiosity, is this along the lines of the behavior you're looking for?
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:
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.
+4Ben 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!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.