When a field name contains spaces, it must be wrapped in curly braces (like how that rhymes?) :winking_face: Also, double-check your quotes (unless they just got curled by posting here).
VALUE(TRIM(RIGHT(SUBSTITUTE(LEFT({Expenses Accounts}, LEN({Expenses Accounts}) - 2), " ", REPT(" ", 10)), 12)))
FWIW, to force the forum parser to format text as pre-formatted text, indent single lines with four spaces (as above). For blocks of code, type a triplet of inverted apostrophe characters ( ` ) on their own lines both before and after. The text in this screenshot:
…becomes this when formatted…
This text is pre-formatted.
Some text becomes automatically color-coded this way, like "strings".
Welcome to the community, @Laura_Flores! :grinning_face_with_big_eyes: I’m guessing you’re going for a more general approach—some records might have a different word (or series of words) in place of “FEASIBILITY”, different numbers, etc.—so I built my solution to not look for too many specific features in the text. The only things I’m counting on being consistent are the separators (" - "), and each task name/label ending in a question mark.
To solve this, I actually found it easier to work backwards. As you found, pulling {TASK'S WEIGHT} off the end with RIGHT() is easy. From there, I used that extraction to aid in pulling off the number before it for {GATE'S WEIGHT}, and the combination of those two to extract the text for {GATE} (“FEASIBILITY”, or whatever text may be after the question but before the first number). And I didn’t need to use TRIM(). :slightly_smiling_face: Here are my test results:
And here are the formulas. First for {GATE}:
MID(SUBSTITUTE({PROJECT TASKS}, " - " & {GATE'S WEIGHT} & " - " & {TASK'S WEIGHT}, ""), FIND("? - ", {PROJECT TASKS}) + 4, 50)
Next for {GATE'S WEIGHT}:
IF({PROJECT TASKS}, VALUE(RIGHT(SUBSTITUTE(SUBSTITUTE({PROJECT TASKS}, " - ", "|"), "|" & {TASK'S WEIGHT}, ""), 2)))
And finally {TASK'S WEIGHT}:
IF({PROJECT TASKS}, VALUE(RIGHT({PROJECT TASKS}, 2)))
Notice that I wrapped the numerical extractions in VALUE() to turn them into numbers, in case you want to use those in any other formula calculations.
@Louise_Nightingale Regex to the re(gex)scue! :winking_face:
IF(Currency, REGEX_EXTRACT(Currency, "[\\d,]+"))
Breakdown…
- The
\dtoken matches any digit (the extra backslash is required to escape the backslash needed by the token), and the comma matches a literal comma - The brackets tell the regex interpreter to match any of the contained characters/tokens
- The
+at the end tells the interpreter to make that previous match one or more times
