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

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?

Screen Shot 2021-05-20 at 11.14.00 AM

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.

2 Likes

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

KeySmart-Red.png

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.