Some exports print negatives as 1,200.00- with the minus at the end, and others as (1,200.00) in brackets. Excel reads both as text, so they never add up. For a trailing minus, use =IF(RIGHT(TRIM(C2),1)="-",-VALUE(SUBSTITUTE(LEFT(TRIM(C2),LEN(TRIM(C2))-1),",","")),VALUE(SUBSTITUTE(TRIM(C2),",",""))). Brackets need a different formula, shown below.
How do you convert 1,200.00- to -1200?
The formula checks the last character. If it is a minus, it drops that character, removes commas, converts to a number and flips the sign. If it is not, it just converts the text.
- =IF(RIGHT(TRIM(C2),1)="-",-VALUE(SUBSTITUTE(LEFT(TRIM(C2),LEN(TRIM(C2))-1),",","")),VALUE(SUBSTITUTE(TRIM(C2),",","")))
- 1,200.00- becomes -1200.
- 450.00 stays 450.
- A normal leading-minus entry such as -75.50 also passes through correctly, because the second branch handles it.
And what about (1,200.00) in brackets?
Accountants like brackets for negatives. In a worksheet they need stripping, then the sign flipping:
- =IF(AND(LEFT(TRIM(C2),1)="(",RIGHT(TRIM(C2),1)=")"),-VALUE(SUBSTITUTE(MID(TRIM(C2),2,LEN(TRIM(C2))-2),",","")),VALUE(SUBSTITUTE(TRIM(C2),",","")))
- MID(text,2,LEN(text)-2) cuts off the opening and closing bracket in one go.
- (1,200.00) becomes -1200 and 3,400.00 stays 3400.
Excel sometimes converts bracketed text on its own when you type it in directly, but a column pasted in as text is not reliable, so do not depend on that behaviour.
| Text in the cell | Formula to use | Result |
|---|---|---|
| 1,200.00- | Trailing-minus formula | -1200 |
| (1,200.00) | Bracket formula | -1200 |
| (18,000.00) | Bracket formula | -18000 |
| 2,350.00- | Trailing-minus formula | -2350 |
| 52,000.00 | Either formula | 52000 |
Is there a no-formula shortcut for trailing minus?
Yes. Text to Columns has a setting built for exactly this. Select the column, go to Data, then Text to Columns, choose Delimited and press Next twice. On the last page click Advanced, tick Trailing minus for negative numbers, and check that the decimal separator is a full stop and the thousands separator a comma. Press OK, then Finish. Excel converts 1,200.00- to -1200 in place.
The shortcut changes the column itself, so make a copy of the sheet first. It also only knows about the trailing minus, not brackets, so a column that mixes both still needs the formulas.
What if the numbers still fail?
If you get #VALUE!, the cell holds something else besides the digits, minus or brackets. Typical suspects are a rupee sign, a Dr or Cr suffix, or a non-breaking space that TRIM cannot remove. Use =LEN(C2) to compare with the characters you can count by eye. Fix the leftover character first and the formula will start working.
Worked example
Say a month's refunds and fees appear as 450.00-, (1,299.00) and 25.00-. The formulas return -450, -1299 and -25, which sum to -1,774. The SUM of the converted column should equal that figure, and the unsigned text version would have shown 0.
Can you show brackets again once the numbers are real?
Yes, and this is the tidy way to get the accountant's look. Convert to true negatives first, then apply a number format that draws brackets: in Format Cells choose Number and pick the style that shows negatives in brackets, or use the custom format #,##0.00_);(#,##0.00). The cell still holds -1200; only the display changes, so SUM and SUMIFS keep working.
If one export mixes both styles in a single column, convert the bracketed rows in one helper column, the trailing-minus rows in another, and let ISNUMBER tell you which helper produced a figure for each row. A row where neither worked needs a look by eye.
Whichever route you use, keep the original text column until the totals agree with the statement.
Closing checklist
- Pick the formula that matches the pattern in your export.
- Fill it into a helper column rather than overwriting the original.
- Count negatives before and after.
- Paste as values when the totals match the statement.
For regular statement uploads, SpendixAI can read PDF, CSV or Excel files and lets you review rows before saving, which is handy when you would rather not maintain formulas.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


