Split dimensions into two parts

split-dimensions-into-two-parts

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

The example contains a list of custom text dimensions as raw data. 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

Steps to split dimensions into two different parts:

  1. Open Excel.
  2. Type =SUBSTITUTE(SUBSTITUTE(B3,”m”,””),” “,””)
  3. Press Enter.
  4. The formula splits the dimensions into two parts.

Explanation

Extracting the numbers from the cell is not easy, so we’ll show you two different methods. Extracting individual dimensions from a text can be performed with built-in Excel formulas that combine several text manipulation functions. Our worksheet contains text dimensions (for example, “10 m x 85 m”). The dimensions include both the “m” unit and space characters (” “).

In a nutshell, we’ll use the steps below:

  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
split-dimensions-into-two-parts

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

=SUBSTITUTE(SUBSTITUTE(B3,”m”,””),” “,””)

Check the result in cell D3. Then, using the formula mentioned earlier, we can remove the unnecessary spaces and units. 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 sections. The formula in cell F3:

=LEFT(B3,FIND(“x”,B3)-1)

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

strip-the-left-part

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

=RIGHT(D3,LEN(D3)-FIND(“x”,D3))

split-dimensions-into-two-parts-and-get-the-right-part

Okay, take a look at the other solution!

Using the SUBSTRING function

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.

using-the-CONCAT-and-SUBSTRING-functions

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

  1. =Substring(B3,” “,1) = 44
  2. =Substring(B3,” “,3) = x
  3. =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.