Function Problems

How to Display a Currency Abbreviation Before an Excel Value

Emma BrownEmma Brown Oct 10, 2026 870 views

Question details

The user wants to dynamically add a specific currency abbreviation (like USD or INR) as a prefix to a numeric value based on a selection from another cell.

How to Display a Currency Abbreviation Before an Excel Value
Product
Excel
Device & OS
not provided
Scenario
Formatting financial or sales data where multiple currencies are used, and amounts need to be clearly labeled with their respective dynamic currency abbreviations.
Observed behavior
The goal is to output a combined text string showing the correct abbreviation and formatted amount (e.g., USD 100.00), since natively modifying a single numeric cell's prefix dynamically requires VBA.
Before you start

Ensure you have a reference table set up with your full currency names in one column and their corresponding abbreviations in the adjacent column so that the lookup formula can reference it correctly.

Solution 1Recommended

Use XLOOKUP and TEXT Formula in a Separate Cell

The most efficient way to combine a dynamic currency abbreviation with a formatted number is to use a lookup formula alongside the TEXT function in a new cell.

Dynamically altering the physical number format of a single cell based on another cell's value requires VBA code. To avoid macros, you can use a formula to combine the lookup result and the number into a text string in a separate cell.

1
Set up a mapping table

Create a table (e.g., in cells AA2:AB55) where column AA contains currency names and column AB contains the target abbreviations.

2
Select the target cell

Click on the cell where you want the combined currency abbreviation and formatted amount to appear.

3
Enter the combination formula

Assuming B1 has the currency name and C1 has the numeric amount, enter the formula: =XLOOKUP(B1,$AA$2:$AA$55,$AB$2:$AB$55)&" "&TEXT(C1,"0.00").

4
Apply the formula

Press Enter to see the formatted result, such as 'USD 100.00'. You can drag the fill handle down to apply this formula to the rest of the column.

Use XLOOKUP and TEXT Formula in a Separate Cell
No VBA Required: This method safely outputs a text string without needing complex macro programming, keeping your workbook lightweight and easy to maintain.

Easily Format Financial Data with WPS Spreadsheet

WPS Spreadsheet offers full compatibility with advanced Excel formulas like XLOOKUP, VLOOKUP, and TEXT, making it simple to manage multi-currency datasets and format your financial reports dynamically.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your financial data.
  2. 2. Prepare the lookup table: Ensure your currency mapping table is set up in a separate sheet or off-screen range.
  3. 3. Apply the lookup formula: Type your XLOOKUP and text concatenation formula directly into the target cell.
  4. 4. Format instantly: Press Enter and drag the fill handle to instantly format your currency values across the entire column.
Fully supports advanced lookup and text combination formulas like XLOOKUP and TEXT.100% compatible with Microsoft Excel (.xlsx) formats for seamless file sharing.Lightweight, fast, and completely free to use for your daily financial data processing needs.
microsoft office alternative - wps office

Frequently Asked Questions

Can I format the original numeric cell dynamically without changing it to text?

To dynamically change the number format prefix of a single cell based on another cell's value without converting it to text, you must use VBA macros, as standard Excel formulas cannot alter cell formatting rules directly.

What if my version of Excel does not support XLOOKUP?

If you are using an older version of Excel, you can use VLOOKUP instead. The formula would look like this: =VLOOKUP(B1,$AA$2:$AB$55,2,FALSE)&" "&TEXT(C1,"0.00").

Will the combined currency and amount still work in mathematical calculations?

No. Because the formula uses the & operator and the TEXT function, the final result is recognized as a text string. If you need to perform calculations like SUM or AVERAGE, you must refer to the original numeric cell rather than the combined text cell.