How to Split Dimensions Into Two Parts in Excel

Why This Task Matters in Excel

Product dimensions appear everywhere: e-commerce catalogues, engineering drawings, warehouse pick lists, logistics manifests, furniture specifications, event stage layouts, and more. They usually arrive in a single text field such as “24 x 36”, “3.5 × 5”, or “12x18x0.75”. While the single string looks tidy on paper, it is not analysis-friendly. Operations, purchasing, and reporting teams frequently need the individual numbers—width and height—to:

• calculate carton or pallet volume
• select the correct packaging materials
• determine postage bands based on the larger side
• drive conditional formatting (“highlight anything wider than 40 cm”)
• feed dimensions into 3-D modeling or pricing calculators
• build pivot tables that summarise by standard size buckets

In retail, for example, splitting “8 × 10” photo sizes into separate width and height columns allows the merchandising team to group by aspect ratio, compare frame compatibility, or drive bundle recommendations. In construction, separating “150 mm × 90 mm” timber sizes lets quantity surveyors multiply length × width to find the cross-sectional area.

Excel is ideal for this task because it offers:

• Text‐centric functions (TEXTSPLIT, LEFT, MID, SEARCH)
• Point-and-click features (Text to Columns, Flash Fill)
• Query tools (Power Query’s Split by Delimiter)
• A formula engine that automatically updates when new rows arrive

Failing to split the dimension can lead to copy-paste mistakes, manual retyping, hidden leading spaces that break lookups, and analytical blind spots because width and height behave differently in models. Mastering this skill keeps your data tidy, automatable, and interoperable with any downstream process that expects numeric inputs rather than a single descriptive string.

Finally, the logic behind splitting dimensions overlaps with many other Excel skills—parsing order numbers, extracting SKU codes, splitting first-last names, or isolating domain names from email addresses. Learning the techniques here therefore levels up your general text-processing proficiency.

Best Excel Approach

In modern Microsoft 365 or Excel 2021, the fastest, most robust solution is TEXTSPLIT. It recognises multi-character delimiters (e.g., space-x-space), returns a dynamic spill array, and automatically trims extra white space. The core idea is to split the string on its delimiter (usually “x”, “X”, or “×”) and capture the first two elements.

Syntax we will use:

=LET(
    parts, TEXTSPLIT(A2, "x", , TRUE),
    width, INDEX(parts, 1),
    height, INDEX(parts, 2),
    CHOOSE({1,2}, width, height)
)

Why this beats older methods:

• Only one formula per row or column; no helper columns needed
• Case-insensitive (handles both “x” and “X”)
• Optional trim argument removes stray spaces automatically
• Can be wrapped in LET/CHOOSE to output two side-by-side columns from a single formula
• Dynamically resizes; when you add or delete rows, the spill range adjusts

When should you not use TEXTSPLIT?

• Pre-365 versions where the function does not exist
• Files destined for colleagues on Excel 2016 or earlier
• Data arriving with exotic delimiters like the Unicode multiplication sign “×” intermixed with “x” where a custom split may be safer

For those cases, older but still effective alternatives include LEFT-FIND-MID formulas, Text to Columns, Flash Fill, or Power Query’s Split Column by Delimiter. We will examine each later.

Parameters and Inputs

Successful splitting depends on understanding your inputs:

Required

• Source cell (text) – The dimension string such as “24 x 36” or “4x8”.
  Optional / configurable
• Delimiter – normally “x” but could be “×”, “X”, “ by ”, or a hyphen.
• Ignore case – TEXTSPLIT treats delimiters case-sensitively unless you force a consistent lower case first.
• Maximum splits – we usually want the first two parts; a third depth value should be ignored or handled separately.
• Trim white space – stray spaces before or after numbers must be removed to convert them to numeric type. TEXTSPLIT’s fourth argument handles this automatically; otherwise use VALUE(TRIM()).

Data preparation tips

• Replace smart quotes and multiplication symbols (“×”) with “x” if possible, or include both in the delimiter array.
• Ensure there is only one delimiter between numbers; “24xx36” will create blank elements.
• Decide whether fractions (e.g., “5-3/4 x 8”) should stay as text or be converted to decimals for math. The VALUE function will error on fractional text unless you substitute “-” with space.
  Validation
• Confirm each string includes at least one delimiter; use IFERROR to flag exceptions.
• Ensure numeric conversion returns numbers; check with ISNUMBER.
  Edge cases
• Empty cells, leading delimiters, trailing delimiters, or strings without the delimiter must be trapped to avoid spill errors or #VALUE!.

Step-by-Step Examples

Example 1: Basic Scenario

Business context
A craft-store manager receives a supplier price list with a Dimension column in [A2:A10] containing values like:

She needs separate Width and Height columns for a pivot that groups products by the longer side.

Steps

1. Insert blank columns B and C headed “Width” and “Height”.
2. In B2 enter:

=LET(
    parts, TEXTSPLIT(A2,"x",,TRUE),
    INDEX(parts,1)
)

3. In C2 enter:

=LET(
    parts, TEXTSPLIT(A2,"x",,TRUE),
    INDEX(parts,2)
)

4. Because TEXTSPLIT returns text, nest VALUE() around INDEX if you plan numeric calculations:

=VALUE(LET(parts,TEXTSPLIT(A2,"x",,TRUE),INDEX(parts,1)))

5. Drag the two formulas down or, in 365, wrap them in CHOOSE so a single spill outputs both columns:

=LET(
    parts, TEXTSPLIT(A2,"x",,TRUE),
    CHOOSE({1,2}, VALUE(INDEX(parts,1)), VALUE(INDEX(parts,2)))
)

Expected result

Why it works
TEXTSPLIT splits on the lowercase “x”. The trim argument removes the spaces, leaving clean substrings “8” and “10”. INDEX selects the first and second. VALUE converts them to numbers so SUM or MAX works later.

Troubleshooting

• If you see #CALC!, check that every row contains the delimiter.
• If you get text numbers (aligned left), wrap VALUE.
• If some rows use uppercase “X”, change the delimiter to [\"x\",\"X\"] or preprocess with LOWER().

Example 2: Real-World Application

Scenario
A fulfilment centre imports product specs from multiple sellers. The Dimension column mixes delimiters, spaces, and even the multiplication sign:

The operations analyst needs automated width and height extraction for 15 000 SKUs and must share the workbook with partners still on Excel 2016.

Steps using legacy formulas

1. Standardise delimiters by converting every variant to lowercase “x”. In B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"×","x")," x ","x")," ","")

Drag down. The helper column now shows clean “4x6”, “5x7”, etc.

2. Find the position of “x”.

=FIND("x",B2)

3. Extract width:

=VALUE(LEFT(B2, FIND("x",B2) - 1))

4. Extract height:

=VALUE(MID(B2, FIND("x",B2) + 1, LEN(B2)))

5. Hide or delete the helper column if desired.

Explanation

• LOWER makes the string case-consistent.
• SUBSTITUTE swaps unusual delimiters with “x” and removes spaces so the FIND location is predictable.
• LEFT and MID carve the text before and after the “x”.
• VALUE turns text to numbers for calculations.

Integration

Create a Table (Ctrl + T) so new SKUs automatically expand the formulas. Build a pivot with a calculated column LongSide `=MAX(`[Width],[Height]) to slot products into packaging tiers.

Performance considerations

With 15 000 rows, these simple volatile-free functions calculate instantly. Using one helper column and two end formulas is memory-efficient and compatible with Excel 2007+.

Example 3: Advanced Technique

Context

A cabinetry firm stores panel sizes including thickness: “30x60x0.75”, “24 x 48 x 0.5”. They often need just the first two dimensions for board-cutting optimisation but must sometimes keep thickness in a third column. They also plan to switch to Microsoft 365 soon but remain on 2019 for now.

Goal

Create a universal formula that:

• works in 2019
• handles optional third size
• ignores trailing delimiters
• trims excess spaces
• converts numeric text (including fractions like “0.75”) to numbers

Solution – nested SUBSTITUTE, TEXT functions, and IFERROR

1. Clean delimiter and condense spaces:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,"×","x")," X ","x"))

2. Locate first and second “x” positions with FIND and SUBSTITUTE:

=FIND("x",A2)
=FIND("x", SUBSTITUTE(A2,"x","~", 2))

The tilde marker tells SUBSTITUTE to replace the second occurrence only, allowing us to find it.

3. Width:

=VALUE(LEFT(A2, FIND("x",A2)-1))

4. Height (if present):

=LET(
    first, FIND("x",A2),
    second, IFERROR(FIND("x",SUBSTITUTE(A2,"x","~",2)), LEN(A2)+1),
    VALUE(MID(A2, first+1, second-first-1))
)

5. Thickness (optional third):

=IFERROR(
    VALUE(MID(A2, second+1, LEN(A2))),
    ""
)

Wrap the entire set inside a dynamic spilled CHOOSE if you upgrade to 365 later.

Professional tips

• Use LET even in 2019 for readability (it is supported).
• Channel all cleaning steps into one LET variable to avoid recalculating.
• For fractional inputs like “5-3/4”, pre-convert “-” to a space then use VALUE() which interprets common fractional notation.

When to choose this over simpler methods

• Source strings have an optional third number.
• You need full backward compatibility.
• You want precise control over each dimension without external add-ins.

Tips and Best Practices

1. Normalise delimiters first – Replace every variant (uppercase, spaces, Unicode ×) with a single “x” so your split logic is bullet-proof.
2. Turn text into numbers immediately – Wrap VALUE or use double negative (--) to avoid surprises with numeric comparisons later.
3. Isolate cleaning logic – Perform SUBSTITUTE / TRIM in a helper column or LET variable so the split formula stays readable.
4. Spill responsibly – In 365, reserve two blank columns to the right of your spill or convert to a Table to prevent overwrite warnings.
5. Use data validation – Require new inputs to contain “x” so accidental typos surface quickly.
6. Document the delimiter – Add a comment or named constant such as Delim=\"x\" so future editors grasp why FIND searches for that character.

Common Mistakes to Avoid

1. Forgetting to trim spaces – A leading space before “8” makes VALUE fail. Always set trim=TRUE in TEXTSPLIT or apply TRIM.
2. Assuming every string has the delimiter – When a blank row sneaks in, TEXTSPLIT returns #N/A. Wrap the formula in IFERROR or ISNUMBER(SEARCH()).
3. Leaving numbers as text – Lookups may work, but maths returns zero. Convert with VALUE or multiply by 1.
4. Overwriting spill ranges – Typing in a cell that a dynamic array wants to occupy throws a #SPILL! error. Plan blank columns or use CHOOSECOLS to direct outputs.
5. Not handling third dimensions – If “24x36x1” appears, a two-part split may silently drop the last piece, causing inaccurate height extraction. Validate second FIND position to catch this.

Alternative Methods

When to choose each

• Quick one-off task – Text to Columns or Flash Fill.
• Ongoing report but no 365 – LEFT-FIND-MID formulas.
• Large datasets, scheduled refresh – Power Query.
• Modern Excel users – TEXTSPLIT with LET.

Migration tip: Build a formula approach first; later, replace with TEXTSPLIT simply by swapping the extraction logic—column headers and downstream references stay identical.

FAQ

When should I use this approach?

Use splitting whenever a single dimension string needs to feed calculations that treat width and height differently—shipping cost, board-foot computation, or image scaling. It is also essential before creating pivot tables or charting size distributions.

Can this work across multiple sheets?

Yes. Point TEXTSPLIT or LEFT-FIND-MID to external sheet references like \'Orders 2024\'!A2. If width and height need to reside in the same sheet as the source, keep formulas local; if they belong in a model sheet, reference the raw data sheet and let the dynamic array spill across.

What are the limitations?

TEXTSPLIT requires Microsoft 365/2021. Legacy formulas struggle with mixed delimiters unless pre-cleaned. Fractions or units (e.g., “cm”) need extra parsing. Maximum of 32 767 characters applies, but dimension strings seldom reach that.

How do I handle errors?

Wrap extraction in IFERROR to substitute blank or warning text. Use ISNUMBER to test VALUE results. In Power Query, enable “Keep errors” to inspect problematic rows then decide to correct or remove.

Does this work in older Excel versions?

Yes—LEFT-FIND-MID, Text to Columns, Flash Fill, and Power Query (via free add-in) all run in Excel 2010-2019. Only TEXTSPLIT is version-specific.

What about performance with large datasets?

Formulas with FIND and MID are lightweight; even 100 000 rows calculate instantly. TEXTSPLIT is vectorised and efficient. Power Query handles millions of rows by streaming. Avoid volatile functions like INDIRECT which recalculate constantly.

Conclusion

Splitting dimension strings into separate width and height values transforms a descriptive field into actionable numbers. Whether you leverage the modern TEXTSPLIT dynamic array or rely on classic FIND-MID formulas, mastering this technique unlocks cleaner analytics, faster reporting, and fewer manual errors in any workflow that manipulates sizes. Continue practising on mixed-delimiter datasets, explore Power Query for enterprise-scale imports, and soon you will instinctively convert any concatenated text into structured, analysis-ready columns.