Split dimensions into two parts

Split dimensions into two parts! This tutorial will show you how to extract the left and right dimensions into separate values.

We have a list containing custom text dimensions as raw data in the example. Our goal is to split the original text into two parts. First, we’ll use formulas based on regular Excel functions and -as usual- user-defined functions.

How to Split dimensions into two parts

Extracting the numbers from the cell is not an easy task. So we’ll show you two different methods.

Split dimensions using SUBSTITUTE

Extracting individual dimensions from a text can perform with built-in Excel formulas that combine several text manipulation functions.

Our worksheet contains text dimensions (for example, “10 m x 85 m”). We have both the “m” unit and space characters (” “) included in the dimensions.

Steps to split dimensions into two different parts:

1. Remove the unwanted characters (spaces) from the original string.
2. Remove the units
3. Extract the left part of the expression
4. Get the right part of the expression

We’ll apply a nested formula using the SUBSTITUTE function.

=SUBSTITUTE(SUBSTITUTE(B3,"m","")," ","")

Check the result in cell D3. Then, with the help of the formula mentioned earlier, we can remove the unnecessary spaces and the units.

Explanation: Evaluate the formula from the inside out!

This nested functions-based formula leaves the numeric parts of the original text. The inner section strips the “m” character:

=SUBSTITUTE(B3,"m","")

The outer section of the formula eliminates unnecessary spaces.

=SUBSTITUTE(SUBSTITUTE(B3,"m","")," ","")

We need only the separator character because, with its help, we can extract the left and the right section. The formula in cell F3:

=LEFT(B3,FIND("x",B3)-1)

And voila, the result is the left numeric part of the original text.

Use the formula below to extract the dimension from the right:

=RIGHT(D3,LEN(D3)-FIND("x",D3))

Using the SUBSTRING function

The first example is a little bit complicated. The best way to normalize text is by using the Substring function. Use our free function library to push the limits in Excel. Check the add-in!

Formula:

=CONCAT(Substring1, Substring2, Substring3)

We already know how the CONCAT function works.

The Substring function returns the nth element of the text string, where a specified separator character separates the parts.

First, combining three substring functions gets the dimensions and the separator character.

=Substring(B3," ",1) = 44
=Substring(B3," ",3) = x
=Substring(B3," ",4) = 20

Finally, join the substrings using the CONCAT function:

=CONCAT(Substring(B3," ",1),Substring(B3," ",3),Substring(B3," ",4))

From now on, apply the LEFT and RIGHT functions to split dimensions into two parts.

Related Formulas and Examples

• Get the first word from some text
• Get the last word from a text string
• Split text with delimiter