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:**

- Open Excel.
- Type =SUBSTITUTE(SUBSTITUTE(B3,”m”,””),” “,””)
- Press Enter.
- 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:**

- 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(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.**

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

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

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.

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****Abbreviate names**