Multiply a Currency Amount by the Correct Exchange Rate in WPS Office Spreadsheets
Question details
The user needs to multiply currency amounts by the correct exchange rate in WPS Office Spreadsheets when the currency type is stored only as cell number formatting, such as £ or $, rather than as text, so the formula must extract the currency code from the number format.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A spreadsheet contains currency amounts formatted with different currency symbols, and the user wants to apply the matching exchange rate from a lookup table.
- Observed behavior
- A normal formula cannot reliably identify the currency from the displayed symbol alone because the symbol is number formatting rather than a cell value.
Before creating the formula, select a currency cell, open Format Cells with Ctrl+1, and confirm the symbol is applied under Currency or Accounting number format rather than typed as text. Also prepare a small exchange rate table with the same currency codes you expect to extract.
Extract the Currency Code with a Named LAMBDA Formula and Apply the Exchange Rate
Use this method when currency amounts are formatted as real number formats and you need a formula that can read the currency code automatically for one cell or a whole range.
Excel and WPS Office store currency symbols as display formatting, so formulas cannot directly read the £ or $ from the cell value. A named LAMBDA formula using GET.CELL(7,ref) reads the number-format code and extracts the currency identifier between square brackets.
In WPS Office Spreadsheets, go to the Formulas tab and click Name Manager.
Click New, enter ExtractCurrencyFormat as the name, and in the Refers to box paste: =LAMBDA(ref,LET(rawFormat,GET.CELL(7,ref),TEXTBEFORE(TEXTAFTER(rawFormat,"["),"]")))
Use =ExtractCurrencyFormat(A2) for a single currency amount, or =MAP(A2:A100,ExtractCurrencyFormat) to spill the currency codes for a range.
Use the extracted code in a lookup formula, for example =A2*XLOOKUP(ExtractCurrencyFormat(A2),$E$2:$E$4,$F$2:$F$4), adjusting the currency code and rate ranges to your table.
Use WPS Office Spreadsheets to Apply Currency-Aware Exchange Rate Formulas
WPS Office Spreadsheets can complete this task with the same LAMBDA, GET.CELL, TEXTBEFORE, TEXTAFTER, and XLOOKUP functions used in Excel. Create the named function, extract the currency code from the cell format, and multiply each amount by the correct rate.
- 1. Download and install WPS Office: Download WPS Office from the official site, install it, and open your .xlsx currency workbook in WPS Office Spreadsheets.
- 2. Open Name Manager from the Formulas tab: Select the Formulas tab, click Name Manager, and choose New to define the ExtractCurrencyFormat name.
- 3. Paste the LAMBDA formula: In the Refers to box, enter =LAMBDA(ref,LET(rawFormat,GET.CELL(7,ref),TEXTBEFORE(TEXTAFTER(rawFormat,"["),"]"))) and click OK.
- 4. Apply the formula and exchange rate: Use =ExtractCurrencyFormat(A2) or =MAP(A2:A100,ExtractCurrencyFormat), then multiply the amount by the matching rate with XLOOKUP or VLOOKUP.
Frequently Asked Questions
Why can’t Excel or WPS Office identify the currency from a displayed £ or $ symbol?
Because currency symbols applied through Format Cells > Currency or Accounting are only number formatting. The underlying cell value remains a number, so formulas see no currency text. Use GET.CELL(7,ref) in a defined name to read the format code.
Can I extract currency codes for an entire column at once?
Yes. After creating the ExtractCurrencyFormat named LAMBDA formula, use =MAP(A2:A100,ExtractCurrencyFormat). The formula spills the currency code for each cell in the range.
What should I do if the pound or dollar symbol is typed directly in the cell?
If the symbol is part of the cell text, GET.CELL cannot treat it as number formatting. Convert the values to real currency formatting or create a helper column that stores the three-letter currency code, then use that helper column in your XLOOKUP.
How do I multiply the amount by the correct exchange rate after extracting the code?
Use the extracted code as the lookup value, for example =A2*XLOOKUP(ExtractCurrencyFormat(A2),currencyCodeRange,exchangeRateRange). This applies the matching rate to the amount in A2.
