Function Problems

How to Decode a Number Set and Convert Millimeters to Inches in Excel

Maira Mehtab Oct 10, 2026 870 views

Question details

The user needs to split a structured product code into individual data points and convert the extracted size values from millimeters to inches.

How to Decode a Number Set and Convert Millimeters to Inches in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting specific numeric segments from a structured text string (such as a product code) and performing mathematical unit conversions on the extracted numbers.
Observed behavior: The product codes must be parsed accurately into separate columns based on specific character positions, with the millimeter dimensions converted into inches.

Before you start

Identify the exact character positions and delimiters (like hyphens) within your product codes to ensure the formulas extract the correct numbers before converting.

Solution 1Recommended

Use Dynamic Array Formulas (LET and TEXTAFTER)

For newer versions of Excel, combining LET, TEXTAFTER, and HSTACK allows you to split strings into multiple columns and convert measurements simultaneously.

This approach uses dynamic arrays to calculate multiple components in a single formula. It automatically divides the extracted string values by 25.4 to convert millimeters to inches.

Select the target cell

Click the cell where you want the split data sequence to start (e.g., cell B2 next to your product code in A2).

Enter the LET formula

Type the formula: =LET(myCode,A2,Half2,MID(TEXTAFTER(myCode,"-"),{1,5,6,7,8,12},{4,1,1,1,4,4}),HSTACK(TEXTBEFORE(myCode,"-"),TAKE(Half2,,4),TAKE(Half2,,-2)/25.4))

Apply the calculation

Press Enter. The formula will automatically spill the extracted segments into adjacent columns. The TAKE(Half2,,-2)/25.4 segment handles the conversion from millimeters to inches.

Use Dynamic Array Formulas (LET and TEXTAFTER)

Version Requirement: This solution requires an Excel version that supports dynamic arrays, such as Microsoft 365 or Excel 2021.

Solution 2

Use Classic Text and CONVERT Functions

If you are using an older version of Excel, you can utilize standard text functions alongside the built-in CONVERT function.

Solution 3

Use Power Query to Split by Fixed Positions

Power Query is a robust tool for splitting complex codes by specific character positions without needing to write long formulas.

Easily Split Text and Convert Units with WPS Spreadsheet

WPS Spreadsheet provides powerful text-processing functions and a built-in CONVERT formula to seamlessly parse product codes and handle unit conversions. It is fully compatible with classic Excel formulas, ensuring a smooth workflow.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your product codes.
2. Extract the numeric values: Select an empty cell and type the =MID() function to extract the specific characters from your product code string.
3. Apply unit conversion: Wrap your extraction formula with the =CONVERT(value, "mm", "in") function to change the extracted dimensions to inches.
4. Fill the column: Drag the fill handle down to apply the extraction and conversion logic to all rows in your dataset.

Free and lightweight office suite for everyday data tasksFull compatibility with Microsoft Excel formulas like MID, SEARCH, and CONVERTUser-friendly interface for managing complex data sets and arraysAvailable across Windows, Mac, Linux, iOS, and Android platforms

microsoft office alternative - wps office

Frequently Asked Questions

How do I extract a number from a string without grabbing it as text?

You can use a double unary operator (--) before text functions like MID or LEFT (e.g., =--MID(A2, 5, 2)). This forces Excel to evaluate the extracted text string as a numeric value, which is required before performing mathematical operations or unit conversions.

Can I use the CONVERT function for other units besides mm and inches?

Yes, the CONVERT function supports a wide variety of measurement units including weight, distance, time, and temperature. For instance, you can convert centimeters to feet using =CONVERT(A2, "cm", "ft").

Why is my dynamic array formula returning a #SPILL! error?

A #SPILL! error occurs when an array formula tries to output multiple results into adjacent cells, but those cells already contain data. To fix it, clear the cells to the right or below the formula so the array has enough empty space to expand.

Excel & Data Problems