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.

`=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.

### 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.

Use the formula below to extract the dimension from right:

`=RIGHT(B2,LEN(B2)-FIND("x",B2))`

### 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.