Function Problems

How to Calculate a Range-Based Multiplication Result in One Excel Cell

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

The user wants to multiply a single numeric value by a range of values represented as text in another cell (e.g., "27~30") to produce a new calculated text range.

How to Calculate a Range-Based Multiplication Result in One Excel Cell

Product: Excel
Device & OS: not provided
Scenario: Calculating a minimum and maximum range result from a base value and a given range string, and displaying the updated range within a single cell.
Observed behavior: The user needs a formula to automatically extract the numerical boundaries of the text range, multiply each boundary by the base value, and combine them back into a text range format.

Before you start

Ensure that the cell containing the range (e.g., "27~30") is formatted as Text so that Excel does not attempt to interpret the hyphen or tilde as a mathematical minus sign or a formula error.

Solution 1Recommended

Use TEXTBEFORE and TEXTAFTER Functions (Excel 365)

This is the most efficient method for users on modern versions of Excel, leveraging the latest text extraction formulas to isolate the numbers before multiplying.

In newer versions of Excel, you no longer need complex nested functions to extract text. The TEXTBEFORE and TEXTAFTER functions allow you to easily pull out the numbers on either side of the tilde (~).

Locate your data cells

Assume your multiplier (e.g., 50) is in cell A1 and your text range (e.g., 27~30) is in cell B1.

Select the result cell

Click on the cell where you want the final calculated range (1350~1500) to appear.

Enter the formula

Type the formula: =A1*TEXTBEFORE(B1,"~")&"~"&A1*TEXTAFTER(B1,"~") and press Enter.

Use TEXTBEFORE and TEXTAFTER Functions (Excel 365)

Formula Breakdown: This formula extracts the first number, multiplies it by A1, places a literal "~" string in the middle using the ampersand (&) operator, and then appends the multiplied second number.

Solution 2

Use Legacy Text Extraction Functions (Older Excel Versions)

A reliable alternative method using LEFT, RIGHT, MID, FIND, and VALUE for older versions of Excel that do not support dynamic array functions.

Process Text and Data Ranges Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced text manipulation functions like LEFT, MID, FIND, and a wide array of modern formulas. You can effortlessly calculate complex range-based multiplications and modify text strings exactly as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
2. Input your data: Enter your base number in cell A1 (e.g., 50) and your text range in cell B1 (e.g., 27~30).
3. Select the destination cell: Click on cell C1 or wherever you wish to output the calculated result.
4. Apply the extraction formula: Enter the formula =A1*VALUE(LEFT(B1,FIND("~",B1)-1))&"~"&A1*VALUE(MID(B1,FIND("~",B1)+1,LEN(B1))) and press Enter.

100% compatible with Microsoft Excel formulas, functions, and formats.Free, lightweight, and features an intuitive tabbed interface.Built-in advanced text extraction and data manipulation tools.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a hyphen instead of a tilde in the range?

Yes. If your range is written as 27-30, simply replace the "~" in the formula with "-". For example: =A1*TEXTBEFORE(B1,"-")&"-"&A1*TEXTAFTER(B1,"-").

Why does my formula return a #VALUE! error?

This error usually occurs if the delimiter in your formula does not perfectly match the delimiter in the cell (e.g., checking for "~" when the cell contains "-"). It can also happen if there are unexpected blank spaces around the numbers.

How can I handle spaces around the delimiter, like '27 ~ 30'?

You can wrap your extraction functions in the TRIM function to remove extraneous spaces, or include the spaces in your delimiter string within the formula, such as TEXTBEFORE(B1, " ~ ").

Excel & Data Problems