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:
To remove the unit of measurement present along with dimension value, numeric part, you need to use following generic formula:
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.
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:
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
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:
Figure 2. Split dimensions into two parts
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:
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.