Text & Data Cleaning
Split "Width x Height" Dimension Strings into Numeric Columns & Surface Area
Parse messy catalog dimension strings like "24.5 x 18", "120X80 cm", or "60×40" into numeric Width, Height, and Area columns using LOWER, SUBSTITUTE, FIND, LEFT, MID, and VALUE.
Normalizing mixed separators ("x", "X", "×") and unit suffixes before splitting
Supplier product feeds rarely format dimensions uniformly: one vendor sends "24.5 x 18", another sends "120X80 cm", and a third uses the Unicode multiplication sign "×".
Normalizing the string inside LET—lowercasing, converting "×" to "x", and stripping spaces and unit suffixes ("cm", "mm", "in")—reduces every row to clean "W x H" digits around a single "x" delimiter so LEFT, MID, and VALUE extract real numbers for area and shipping-carton math.
| Raw Spec | Normalized String | Numeric Width | Numeric Height | Area (W * H) |
|---|---|---|---|---|
| "24.5 x 18" | "24.5x18" | 24.5 | 18 | 441 |
| "120X80 cm" | "120x80" | 120 | 80 | 9600 |
| "60 × 40 cm" | "60x40" | 60 | 40 | 2400 |
Extract Numeric Width (Left of "x")
Normalizes separators/units first and converts the substring before "x" into a number.
=LET(norm, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2), "×", "x"), "cm", ""), " ", ""), VALUE(LEFT(norm, FIND("x", norm) - 1)))Extract Numeric Height (Right of "x")
Extracts everything after "x" and coerces it with VALUE so Width * Height multiplies directly.
=LET(norm, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2), "×", "x"), "cm", ""), " ", ""), VALUE(MID(norm, FIND("x", norm) + 1, LEN(norm))))Try the example
Use the original values or change them to test your understanding.