< Go Back

Split dimensions into two parts

Split dimensions into two parts

You can split dimensions of text values in MS Excel into two parts using a simple formula. For example, if you have text decisions of (40ft x 250ft) you can split them into two parts using different text functions. In this article, we will learn how to split dimensions into two parts where data is presented as text value rather than a numerical value.

It is more common in practice that measurements are given along with units of measurement, like ft, cm, mm. Therefore, in order to get the numeric values of dimension only, you need to first replace units of measurement with empty space, and then you can split dimensions into two parts (numeric part).

So if you are making a report or any other calculation, it will be more convenient if you have numerical data. This can be extracted by splitting the dimensions using different text functions:

Formula 1

To remove the unit of measurement present along with dimension value, numeric part, you need to use following generic formula:

=SUBSTITUTE(SUBSTITUTE(value,"unit_of_measure","")," ","")

Explanation

If we have “ft” as the unit of measurement and space characters as part of the dimensions, so to get numerical data we will have to remove these first. This will, in turn, simplify the overall equation.

In order to remove ft unit and space “ “, we will use the SUBSTITUTE function. The SUBSTITUTE function uses the formula which results in the subtraction of ft unit and spaces from the dimensions.

Example

Suppose we have a table where column B contains dimensions along with units of measurement, and we want to get rid of these units values from dimensions values in column C. So we will  use following formula to remove units from dimensions values:

=SUBSTITUTE(SUBSTITUTE(B7,"ft","")," ","")

This formula will generate the results as shown in column C of figure 1, by replacing the units and space with an empty value.  

Figure 1. SUBSTITUTE function

Formula 2

Now we need to get the left value of dimension from the value returned in column C by SUBSTITUTE formula as shown in figure 1 of above example. To get the left value, the following formula will be used in column D:

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

Figure 2. Split dimensions into two parts

Formula 3

To get the right value of dimension from the value returned in column C by SUBSTITUTE formula as shown in the example given in Figure 1, the following formula will be used in column E:

=RIGHT(C7,LEN(C7)-FIND("x",C7))

 

Figure 3. Split dimensions into two parts

By using the above three formulas you can easily split dimensions into two parts as left and right dimension values.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar