How to Display Negative Currency Values Inside a CONCATENATE Formula in WPS Spreadsheets
Question details
The user needs to include negative currency amounts in a concatenated text string in WPS Spreadsheets and have them appear in the expected dollar format.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a text string with CONCATENATE or the ampersand operator that combines labels and currency values, including negative amounts.
- Observed behavior
- Negative dollar values do not display as expected because the TEXT function format code lacks a separate negative-number section.
Open the workbook in WPS Spreadsheets and confirm that the cells you are joining are numeric values rather than text already typed with a dollar sign. Decide whether negatives should appear as $-50.00 or ($50.00) before applying the format code.
Add Positive, Negative, and Text Sections to the TEXT Format Inside CONCATENATE
Use this when negative currency values must display with an explicit minus dollar sign and text entries should not break the formula.
The TEXT format code uses semicolon-separated sections: positive;negative;text. If only one section is supplied, WPS Spreadsheets uses it for all values, which is why negative dollar amounts may appear incorrectly inside a concatenated string.
Click the cell where the combined text should appear, then type =CONCATENATE( in the formula bar.
Type the first part of the text string, such as "Total: ", followed by a comma.
For the currency cell, enter TEXT(A2,"$##.00;$-##.00;@") as the next argument. Adjust A2 to the cell that contains the numeric value.
Close the parentheses and press Enter. Drag the fill handle down if the same formatting is needed for additional rows.
Use the Concatenation Operator Instead of CONCATENATE
Use this if you prefer a shorter formula that performs the same text join with the same custom currency formatting.
Apply Custom Currency Formatting in WPS Spreadsheets CONCATENATE Formulas
WPS Spreadsheets lets you combine custom TEXT format sections with CONCATENATE or the ampersand operator so negative dollar values appear correctly in generated text strings without changing the underlying numeric data.
- 1. Open Your Workbook: Open WPS Office and load the spreadsheet that contains the currency values.
- 2. Start the Concatenated Text Formula: Click the cell where the combined text should appear, then type =CONCATENATE( or =.
- 3. Add the Custom TEXT Format: Enter the TEXT function for the numeric cell using the format "$##.00;$-##.00;@".
- 4. Finish and Copy the Formula: Press Enter, then drag the fill handle to apply the same pattern to other rows.
Frequently Asked Questions
Why do negative currency values appear without a minus sign inside CONCATENATE?
The TEXT function format may only contain a positive-number section. Use a three-section format such as "$##.00;$-##.00;@" so negative values use the second section.
Can I show negative currency values in parentheses instead of with a minus sign?
Yes. Change the negative section in the TEXT format, for example "$##.00;$(##.00);@", to display negative dollar amounts in parentheses.
Is CONCATENATE required, or can I use the ampersand operator?
Both work. A formula like ="Total: "&TEXT(A2,"$##.00;$-##.00;@") gives the same result as using CONCATENATE with the same TEXT format.
Does the TEXT format change the original numeric cell?
No. TEXT only changes how the value appears inside the concatenated text string. The original numeric cell remains a number and can still be used in other calculations.
What does the @ section do in the format code?
The @ section controls text values. If the cell referenced by TEXT contains text instead of a number, the @ section displays that text and helps prevent unexpected output.
