Hello, I am looking to extract the individual dimensions of an object from a cell that contains them as a LXWXH string. for example, I have a cell that looks like 11’9" X 4’11" X 8’0", and would need those in three different cells as 11’9" | 4’11" | 8’0". Trying to find a formula that would do this for me. Thanks!
If you’re not familiar with regular expressions, [^|]+ looks for a run of characters that aren’t a pipe, and the \| looks for a pipe (the backslash is for escaping the pipe character, which is used as an “OR” in regex). Airtable’s documentation for formula field functions doesn’t mention it, but REGEX_EXTRACT can return just a portion of what it finds, and that’s what we do with the parentheses (([^|]+)).
So, in plain language, the first formula means “look for a run of characters that aren’t a pipe in front of a pipe character, and return only that run of characters”. The second formula means “look for a run of characters that aren’t a pipe between two pipe characters, and only return that run of characters”. The third formula means “look for a pipe character, then a run of non-pipe characters, then another pipe character, then another run of non-pipe characters, and only return the second run of non-pipe characters”. (That last one is probably not as elegant as it could be, but it’s straightforward.)
The TRIM function around each just removes the whitespace if it’s there (it could actually be integrated into the regex function – for example \|[^|]+\|\s*[^|\s]+ for the third formula – but I would only recommend it if the Label text is machine-generated and there won’t be any instances of someone putting a space between the feet and inch numbers (e.g. 4' 5").