One problem with dimensions entered as text is that they can’t be used for any kind of calculation.
So, in addition, we want our final dimensions to benumeric.
In this case, the delimiter is the “x” character.
There are two basic approaches to solving this problem.
If you are usingExcel 365, the easiest solution is to use theTEXTSPLIT functionas shown in the worksheet above.
Both approaches are explained below.
First, TEXTSPLIT splits the text in B5 using the “x”.
Our goal is to get actual numeric values, so in the second step, we simply add zero.
The formula returns this result to cell D5, and the three dimensionsspillinto the range D5:F5.
Legacy Excel
InLegacy Excel, we need to use more complicated formulas to accomplish the same thing.
We then subtract from that the location of the first “x” + 1.
At a high level, we are using theRIGHT functionto extract text from the right.
TEXTSPLIT can split text into rows or columns.
For example, =LEFT(“apple”,3) returns “app”.
For example, =RIGHT(“apple”,3) returns “ple”.
For example, =MID(“apple”,2,3) returns “ppl”.
LEN will also count characters in numbers, but number formatting is not included.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
SUBSTITUTE is case-sensitive and does not support wildcards….
Related videos
TEXTSPLIT with numbers
