Split dimensions into two parts

Split dimensions into two parts! In this tutorial, we will show you how to extract left and right dimensions into separate values.

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

Let us see the example! Our worksheet contains text dimensions (for example “10 m x 85 m”)

initial data set

How to Split dimensions into two parts

As a first look, extract the numbers from the cell, not an easy task. We’ll show you two different methods.

Example: Split dimensions using SUBSTITUTE

Extracting individual dimensions from a text representation can be done with formulas that combine several text functions.

In the example, we have both the “m” unit and space characters (” “) included in the dimensions.

Steps to split dimensions into two different part:

  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(A2,"m","")," ","")

Check the result in cell B2. With the help of the above-mentioned formula, we can remove the unnecessary spaces and the units.

Steps to Split dimensions into two different part

Explanation

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

=SUBSTITUTE(A2,"m","")

The outer section of the formula eliminates unnecessary spaces.

=SUBSTITUTE(SUBSTITUTE(A2,"m","")," ","")

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

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

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

extract left part dimension

Use the formula below to extract the dimension from right:

=RIGHT(B2,LEN(B2)-FIND("x",B2))
extract right part dimension

Example: using the SUBSTRING function

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

=CONCAT(Substring1, Substring2, Substring3)

We already know how the CONCAT function works. The Substring function returns the nth element of the text string, where the elements are separated by a specified separator character.

Split dimensions into two parts using SUBSTRING

As first, the combination of three substring functions gets the dimensions and the separator character.

=Substring(A3," ",1) = 68
=Substring(A3," ",3) = x
=Substring(A3," ",4) = 77

Finally, join the substrings using the CONCAT function:

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

From now apply the above mentioned LEFT and RIGHT functions to split dimensions into two part.

Related Formulas and Examples