Extracting Length, Width, and Height from a LXWXH cell

[Simon_Risman]

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!

[Russell_Findlay]

I think you may find some joy with these types of formulae

MID({Label},1,5)

MID({Label},SEARCH(“X”,{Label})+1,5)

RIGHT(Label,5)

however the challenge may be - 1) the lengths of strings may vary depending on the actual number of digits in the measure and the input format may get many errors)

You can use other formulae to get string lengths - and with some time may be able to use these numbers in the formulae above but with these formulae we get the following

[Simon_Risman]

Thank you! I can’t seem to get the middle one to work, its saying that it is invalid

[Russell_Findlay]

That’s frustrating - do you have all the brackets etc in ?

Note the function is searching for an X and then looking to the right - so also ask is there am X In the string and is it upper case.

[Andy_Lin1]

Russell_Findlay:

MID({Label},1,5)
MID({Label},SEARCH("X",{Label})+1,5)
RIGHT(Label,5)

I think it might have been the automatic “smart quotes” that get applied to normal (non-code) text on the forum.

Alternatively, you could extract the strings using regular expressions (regex):

TRIM(REGEX_EXTRACT({Label},"([^|]+)\|"))
TRIM(REGEX_EXTRACT({Label},"\|([^|]+)\|"))
TRIM(REGEX_EXTRACT({Label},"\|[^|]+\|([^|]+)"))

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").