Excel Formulas to Split Dimensions by the Multiplication Sign

Manually splitting merged dimension strings in Excel is a tedious, error-prone hurdle for operations analysts. While standard funding sources and asset procurement registries provide raw logistics data, they rarely format physical specifications for immediate database integration. Automating this extraction grants your team the ability to perform instant, scalable shipping and storage calculations.

Stipulation: This methodology requires consistent delimiters throughout your dataset. For example, isolating the individual measurements from a text string like "12x24x36" relies on uniform formatting.

Below, we provide the step-by-step Excel formulas to seamlessly parse your multi-dimensional data into clean, separate columns.

When working with product catalogs, shipping data, or manufacturing specifications in Excel, dimensions are frequently stored as a single text string-such as "10x20x30", "12 x 24", or "5 * 8 * 12". While this format is easy for humans to read, it is notoriously difficult for Excel to analyze. If you need to calculate volume, shipping costs, or sort your inventory by size, you must first split these dimensions into separate columns: Length, Width, and Height.

Depending on your version of Excel, you have access to different tools. Modern users can take advantage of incredibly simple array formulas, while legacy users can rely on creative string-manipulation workarounds. This guide covers how to split dimensions by a multiplication sign using modern formulas, legacy formulas, and native Excel features.

Method 1: The Modern Way Using TEXTSPLIT (Excel 365 & Web)

If you are using Excel 365 or Excel for the Web, splitting text is incredibly easy thanks to the TEXTSPLIT function. This function dynamically splits text across columns (or rows) based on a specified delimiter.

The Basic TEXTSPLIT Formula

If your dimension is in cell A2 and uses "x" as the multiplier, use the following formula in cell B2:

=TEXTSPLIT(A2, "x")

Excel will automatically spill the results into adjacent columns. For example, "10x20x30" will instantly fill three separate cells with 10, 20, and 30.

Handling Spaces and Mixed Delimiters

In real-world datasets, formatting is rarely perfect. You might encounter spaces ("10 x 20 x 30") or different multiplication signs ("*"). You can make your formula more robust by specifying multiple delimiters and nesting it inside a TRIM function:

=TRIM(TEXTSPLIT(A2, {"x","X","*"}))

This formula tells Excel to split the string whenever it finds a lowercase "x", an uppercase "X", or an asterisk (*), while TRIM ensures that any accidental leading or trailing spaces are swept away.

Converting Text to Actual Numbers

Because TEXTSPLIT is a text function, the resulting numbers are outputted as text. This means you cannot perform mathematical calculations (like SUM or PRODUCT) on them immediately. To force Excel to convert these text outputs into true numerical values, apply a double unary operator (--) to the formula:

=--TRIM(TEXTSPLIT(A2, {"x","X","*"}))

Now, your split dimensions are ready for mathematical analysis, volume calculations, and sorting.

Method 2: The Universal "REPT" Trick (Excel 2019 and Older)

If you or your coworkers are using an older version of Excel, you won't have access to TEXTSPLIT. Instead, you must rely on standard text manipulation functions: LEFT, MID, RIGHT, FIND, and SUBSTITUTE.

While you could write custom, highly nested formulas for each column, there is a legendary "universal formula" trick that uses REPT (repeat) and SUBSTITUTE to cleanly split strings of any length.

The Magic Formula

Paste the following formula into your first destination column (e.g., cell B2) and drag it across to C2 and D2:

=TRIM(MID(SUBSTITUTE($A2, "x", REPT(" ", 99)), (COLUMNS($A$1:A1)-1)*99+1, 99))

How It Works

This formula might look like magic, but its logic is beautifully simple when broken down step-by-step:

• SUBSTITUTE($A2, "x", REPT(" ", 99)): This replaces every "x" delimiter with 99 consecutive spaces. Your short string like "10x20x30" suddenly becomes a massive string with huge gaps of whitespace between the numbers.
• COLUMNS($A$1:A1): This acts as a dynamic counter. In the first column, it equals 1. As you drag the formula to the right, it changes to COLUMNS($A$1:B1), which equals 2, and then 3.
• MID(..., (col-1)*99+1, 99): The MID function extracts text from the middle of our massive, space-filled string.
  • In the 1st column, it starts at character 1 ((1-1)*99+1) and grabs 99 characters. This safely captures "10" plus a bunch of trailing spaces.
  • In the 2nd column, it starts at character 100 ((2-1)*99+1) and grabs 99 characters, capturing "20" surrounded by spaces.
  • In the 3rd column, it starts at character 199, capturing "30" and its surrounding spaces.
• TRIM(...): Finally, TRIM strips away all the excess spaces, leaving you with just the clean dimension values.

To convert these legacy results into usable numbers, wrap the entire formula in a VALUE function or add a double negative at the very front:

=--TRIM(MID(SUBSTITUTE($A2, "x", REPT(" ", 99)), (COLUMNS($A$1:A1)-1)*99+1, 99))

Method 3: Classic "Text to Columns" (No-Formula Solution)

If you only need to split dimensions as a one-off task and do not need dynamic formulas that update when the source text changes, Excel's built-in Text to Columns wizard is the fastest option.

1. Select the column of data containing your dimensions.
2. Navigate to the Data tab on the Ribbon.
3. Click on Text to Columns.
4. In the wizard, select Delimited and click Next.
5. Uncheck all standard delimiters and check the box for Other.
6. In the "Other" input box, type your multiplication sign (usually a lowercase x).
7. Click Next.
8. Specify your destination cell (if you don't want to overwrite your original data, change the destination path from $A$2 to $B$2).
9. Click Finish.

This will instantly partition your dimensions into separate, clean numerical columns.

Method 4: Power Query for Automated & Complex Splits

For users handling recurring reports, messy source data, or large datasets (thousands of rows), Power Query is the ideal tool. It records your splitting steps so that when you replace your raw data, you can simply click "Refresh" to repeat the process instantly.

1. Select your range of data and go to the Data tab.
2. Click From Table/Range to import your data into the Power Query Editor.
3. Right-click the column header containing your dimensions.
4. Select Split Column > By Delimiter.
5. In the dropdown menu, choose Custom.
6. Type your delimiter (e.g., x or *).
7. Ensure "At each occurrence of the delimiter" is selected.
8. Click OK. Power Query will automatically detect the data types and convert the split text columns into clean, decimal or integer numbers.
9. Go to the Home tab and click Close & Load to return your cleaned, split columns back to Excel.

Summary of Solutions

Conclusion

Splitting dimension strings doesn't have to be a headache. If you are fortunate enough to use Excel 365, the TEXTSPLIT function turns this chore into a single, highly readable line of code. If you are supporting older workbooks, the classic REPT/SUBSTITUTE formula trick is a robust lifesaver. Whichever method you choose, converting raw dimension strings into structured, numerical columns is the crucial first step to unlocking the full analytical potential of your data.