How to Format a Referenced Dollar Value in WPS Spreadsheets Text
Question details
The user needs to combine text from one worksheet cell with a referenced monetary value so the result displays comma separators and exactly two decimal places.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a text string that includes a currency amount from another worksheet cell, such as a label or summary line.
- Observed behavior
- The referenced dollar value loses currency formatting when concatenated unless the numeric reference is wrapped in the TEXT function.
Confirm that the referenced cell contains a numeric value, not text. The TEXT function only reliably applies currency formatting when it receives a number.
Apply TEXT Around the Referenced Dollar Value
Use this method when the text in the referenced cell does not include a dollar sign and you need the full currency format in the combined result.
The TEXT function converts the referenced numeric value to text using the supplied number format, so commas and two decimal places survive when concatenated with another string.
Click the cell where the combined text and dollar value should appear.
Type ='Cheat Sheet'!A30&TEXT('Cheat Sheet'!B3,"$#,##0.00") and press Enter.
Confirm that the result shows a dollar sign, comma separators, and exactly two decimal places.
Use TEXT Without a Dollar Sign When the Text Already Contains $
Use this variant when the text cell already includes the dollar sign and you only need to add comma separators and two decimal places.
Create Currency-Formatted Text Strings in WPS Spreadsheets
Complete this task directly in WPS Spreadsheets by using the TEXT function to control how a referenced dollar value is displayed inside a text string, then save the file in an Excel-compatible format.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the worksheet with the text and dollar value.
- 2. Select the formula cell: Click the cell where you want the combined text and formatted dollar value to appear.
- 3. Enter the TEXT formula: Type ='Cheat Sheet'!A30&TEXT('Cheat Sheet'!B3,"$#,##0.00") in the formula bar and press Enter.
- 4. Save in Excel format: Go to Menu > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Why does my referenced dollar value lose its formatting when I combine it with text?
Concatenation treats numbers as plain values. Wrap the numeric reference in the TEXT function, such as TEXT(B3,"$#,##0.00"), to preserve currency formatting.
Can I use this TEXT function method across different worksheets?
Yes. Reference the other worksheet by name and cell, for example 'Cheat Sheet'!B3, inside the TEXT function just as you would in a normal formula.
What should I do if the text already includes the dollar sign?
Use a format code without the dollar sign: TEXT('Cheat Sheet'!B3,"#,##0.00"). This prevents two dollar signs from appearing in the result.
Does this method work for currencies other than dollars?
Yes. Replace the dollar sign in the format code with another symbol, such as "€#,##0.00" or "£#,##0.00", while keeping the same TEXT structure.
What happens if the referenced cell is empty or contains text?
If the cell is empty, TEXT returns an empty string. If it contains text rather than a number, the TEXT function may return the text unchanged or cause an error depending on the format code, so check that the source cell is numeric.
