Help

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

Solved
Jump to Solution
748 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Justin_Shook
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

3 Replies 3
Kamille_Parks
16 - Uranus
16 - Uranus

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.

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.