Taking Dimensions listed in one field and extracting them into separate fields

[Justin_Shook]

I have a field in Airtable with L x W x H listed in one cell. The information follows the same format every time. There is a space followed by an x followed by another space.

I would like to pull each value into it’s own cell. I know it would be easier to go the other way around, but we already have a lot of data in this format and it’d be easier for the team entering the information to continue their process.

Can someone help me write a formula to get these 3 values into their own fields?

[Kamille_Parks · Accepted solution]

Your could do this with REGEX_REPLACE()

For each of your three fields, use this formula structure:

VALUE(
   REGEX_REPLACE(
      Name, 
      "(\\d*\\.*\\d*)(\\D*)(\\d*\\.*\\d*)(\\D*)(\\d*\\.*\\d*)", 
      "$#"
   )
)

---

The formula is looking at a five-part pattern: (length)( x )(width)( x )(height), and then replacing the whole thing with a specific piece from that pattern. The piece is chosen by "$#", where # equals some number between (in your case) 1 and 5.

For {Length}, replace "$#" with "$1". For {Width}, replace "$#" with "$3". For {Height}, replace "$#" with "$5".

The entire formula is wrapped in VALUE() to convert the output into a number. Make sure your formula’s formatting allows for integers.

[Justin_Shook]

This worked perfectly. Thank you so much for your help!

[Kamille_Parks]

Great! If you go ahead and mark my first reply as the solution the mods can close out this thread.