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”)
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:
- Remove the unwanted characters (spaces) from the original string.
- Remove the units
- Extract the left part of the expression
- Get the right part of the expression
We’ll apply a nested formula using the SUBSTITUTE function.
Check the result in cell B2. With the help of the above-mentioned formula, we can remove the unnecessary spaces and the units.
This nested functions based formula leaves the numeric parts of the original text. The inner section strip the “m” character:
The outer section of the formula eliminates unnecessary spaces.
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:
And voila, the result is the left numeric part of the original text.
Use the formula below to extract the dimension from right:
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.
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.