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?
Best answer by Kamille_Parks11
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.
+27Your 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.
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!
+27This worked perfectly. Thank you so much for your help!
Great! If you go ahead and mark my first reply as the solution the mods can close out this thread.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.