Formula Errors

How to Multiply Both Numbers in a Hyphen-Separated Range in Excel

Huma Ashraf Ch Sep 28, 2026 870 views

Question details

The user needs to multiply the lower and upper bounds of a hyphen-separated number range stored in a single cell by a specific factor, and output the result in the same hyphen-separated format.

How to Multiply Both Numbers in a Hyphen-Separated Excel Range

Product: Excel / Spreadsheet
Device & OS: not provided
Scenario: Updating price ranges, tolerances, or dimension limits where the data is formatted as a text string (e.g., '20-30') rather than individual numerical cells.
Observed behavior: The user wants to convert a cell containing '20-30' into '2680-4020' by multiplying both bounds by 134, maintaining the single-cell text format.

Before you start

Ensure your text strings do not contain multiple hyphens or inconsistent spacing, as this can cause extraction formulas or the Text to Columns feature to return errors.

Solution 1Recommended

Use Helper Columns to Split, Multiply, and Concatenate

This method is the most reliable approach for all spreadsheet versions. It breaks the process down into manageable steps, minimizing formula errors.

By utilizing the 'Text to Columns' tool or basic text extraction formulas, you isolate the numbers so they can be manipulated mathematically before stitching them back together.

Extract the First Number

Select an empty helper column cell and use the formula =VALUE(LEFT(A1, FIND("-", A1) - 1)) to extract the number before the hyphen.

Extract the Second Number

In the next helper column, use the formula =VALUE(RIGHT(A1, LEN(A1) - FIND("-", A1))) to extract the number after the hyphen.

Multiply the Extracted Values

In adjacent columns, multiply each of your extracted values by your desired factor (e.g., =B1*134 and =C1*134).

Concatenate the Results

Combine the multiplied results into your final format using the ampersand operator. Enter =D1 & "-" & E1 to get the final hyphen-separated string.

Use Helper Columns to Split, Multiply, and Concatenate

Helper Column Benefit: Using helper columns allows you to easily audit your data for extraction errors before applying the final multiplication.

Solution 2

Use a Single Dynamic Array Formula (Modern Excel)

If you are using a newer version of Excel or Office 365, you can use modern array functions to split, calculate, and recombine the text in one single step.

Data Processing Solution

Effortlessly Manipulate Text and Numbers with WPS Spreadsheet

WPS Spreadsheet provides robust text-handling capabilities, making it seamless to parse, calculate, and recombine custom text strings like hyphen-separated ranges without complex workarounds.

1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your hyphen-separated ranges.
2. Split the Text: Select the data column, navigate to the Data tab, and click 'Text to Columns' using the hyphen as a delimiter.
3. Perform Calculations: Multiply the separated columns by your desired factor in the adjacent empty cells.
4. Recombine the Results: Use the '&' operator or CONCATENATE function to join the calculated numbers back together with a hyphen.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx, .xls).Built-in advanced text functions and Text to Columns wizards for easy data splitting.Lightweight software with a familiar, user-friendly interface to boost productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just multiply the cell containing the hyphen directly?

Spreadsheet applications treat a cell containing a hyphen or letters as a text string, not a number. You cannot perform mathematical operations on text, which is why the #VALUE! error occurs. You must extract the numbers first.

What if my range contains spaces around the hyphen, like '20 - 30'?

You can use the TRIM function to remove any leading or trailing spaces after splitting the text. Alternatively, wrap your initial cell reference in the SUBSTITUTE function to remove spaces before extraction: =SUBSTITUTE(A1, " ", "").

How do I apply this to an entire column of ranges?

Once you have set up the formula or helper columns for the first row, simply click the small square at the bottom right corner of the formula cell (the fill handle) and drag it down to apply the exact same extraction and multiplication logic to the rest of the column.

Excel & Data Problems