To split dimensions like "100x50" into two separate parts, you can use formulas based on several functions: LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE.
Note: you can also use Flash Fill in Excel 2013 and above, and the "Text to columns" feature in earlier versions of Excel. Both approaches are simpler than the formulas described below. However, for a formula-based solution, read on.
A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may appear in one text string that includes units, for example:
50 ft x 200 ft
153 ft x 324 ft
In a spreadsheet, it's a lot more convenient to have actual numbers so that you can use them in calculations as you wish.
Extracting individual dimensions from a text representation can be done with formulas that combine several text functions.
In this case, it because we have both the "ft" unit and space characters (" ") included in the dimensions, it makes sense to remove these first. That will "normalize" the dimensions and simplify the formulas that do the actual extraction.
To remove both "ft" and " ", we are using this formula in cell C6, which contains two nested SUBSTITUTE functions:
The FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. This number is used by the LEFT function as the...
In this example, the goal is to extract the first name from a list of names in "Last, First" format as seen in column B. There are several ways to approach this problem, but in this example we are going to extract the last name from the right side...
The 1st dimension To get the first dimension, we are using this formula in C4: = LEFT ( B4 , FIND ( "x" , B4 ) - 1 ) This works by extracting text starting at the LEFT. The number of characters is calculated by locating the first "x" in...
In this example, the goal is to split a text string at the underscore("_") character with a formula. Notice the location of the underscore is different in each row. This means the formula needs to locate the position of the underscore character...
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards.
Some Excel formulas are complex, but all complex formulas are built from simple steps. In this video we build a more complex formula step-by-step.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.