Re: Help extracting a string from a rollup (FIND,MID,LEFT,RIGHT)
Accepted Solutions
This is where regular expressions come in handy. Regular expressions excel at finding patterns, which is ideal for cases where the length will vary, but you have consistent patterns that you can use to determine where to stop. In this case, you would look for the date matching the record date, then extract all characters until you get to a comma. This worked for me:
IF(
AND(Date, Rollup),
REGEX_EXTRACT(
Rollup,
"(?:" & SUBSTITUTE(DATETIME_FORMAT(Date,"DD.MM"), ".", "\\.") & " )([^,]*)")
)
(NOTE: I used a single line text field for the “Rollup” field, but it should still work with an actual rollup field as long as the output is the same as you outlined above)
Here’s how this breaks down…
At the start of the regular expression, we first want to find—but not actually extract—the matching date. The syntax for this find-but-ignore process looks like this: (?:XXX), where XXX is the text/pattern you want to find. In this case we want the date followed by a single space. However the “DD.MM” date format that you’re using includes a reserved REGEX token: the period. In REGEX, a period normally matches any single character, but we want to actually match a period. To do this, we need to escape the period by preceding it with a single backward slash. To actually include a backward slash, though, we need another backward slash in front of it.
To mark the group that we actually want to both match and extract, we surround it with parentheses. Inside those parentheses we need to tell the REGEX parser to match as many characters as possible up to—but not including—the next comma. Individual characters are matched by putting them inside square braces. To match anything except those listed characters, you include the caret symbol— ^ —before the rest. To then indicate that the match should be made as many times as possible, you add an asterisk after the square braces. All of this explains this combo at the end of the regular expression: ([^,]*) In short, extract a group that matches any non-comma character zero or more times. Once it hits a comma, it stops.
When it’s all finished, the expression matches—but doesn’t capture—the record date followed by a single space, then actually captures as many non-comma characters as possible.
This is where regular expressions come in handy. Regular expressions excel at finding patterns, which is ideal for cases where the length will vary, but you have consistent patterns that you can use to determine where to stop. In this case, you would look for the date matching the record date, then extract all characters until you get to a comma. This worked for me:
IF(
AND(Date, Rollup),
REGEX_EXTRACT(
Rollup,
"(?:" & SUBSTITUTE(DATETIME_FORMAT(Date,"DD.MM"), ".", "\\.") & " )([^,]*)")
)
(NOTE: I used a single line text field for the “Rollup” field, but it should still work with an actual rollup field as long as the output is the same as you outlined above)
Here’s how this breaks down…
At the start of the regular expression, we first want to find—but not actually extract—the matching date. The syntax for this find-but-ignore process looks like this: (?:XXX), where XXX is the text/pattern you want to find. In this case we want the date followed by a single space. However the “DD.MM” date format that you’re using includes a reserved REGEX token: the period. In REGEX, a period normally matches any single character, but we want to actually match a period. To do this, we need to escape the period by preceding it with a single backward slash. To actually include a backward slash, though, we need another backward slash in front of it.
To mark the group that we actually want to both match and extract, we surround it with parentheses. Inside those parentheses we need to tell the REGEX parser to match as many characters as possible up to—but not including—the next comma. Individual characters are matched by putting them inside square braces. To match anything except those listed characters, you include the caret symbol— ^ —before the rest. To then indicate that the match should be made as many times as possible, you add an asterisk after the square braces. All of this explains this combo at the end of the regular expression: ([^,]*) In short, extract a group that matches any non-comma character zero or more times. Once it hits a comma, it stops.
When it’s all finished, the expression matches—but doesn’t capture—the record date followed by a single space, then actually captures as many non-comma characters as possible.
