How to Display a Currency Abbreviation Before an Excel Value
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.

- 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.
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.
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.
Create a table (e.g., in cells AA2:AB55) where column AA contains currency names and column AB contains the target abbreviations.
Click on the cell where you want the combined currency abbreviation and formatted amount to appear.
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").
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.

Keep Currency and Amount in Adjacent Columns
Instead of combining them into a single text cell, use adjacent columns to display the currency and amount separately for easier data calculation downstream.
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. Open your dataset: Launch WPS Spreadsheet and open the file containing your financial data.
- 2. Prepare the lookup table: Ensure your currency mapping table is set up in a separate sheet or off-screen range.
- 3. Apply the lookup formula: Type your XLOOKUP and text concatenation formula directly into the target cell.
- 4. Format instantly: Press Enter and drag the fill handle to instantly format your currency values across the entire column.

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.




