Convert Parentheses to Negative Numbers in Excel

TL;DR Bank statements and accounting reports often show negative amounts as (1,250.00), as 1,250.00- with a trailing minus, or with a CR or DR suffix. When that text lands in Excel, it often stays as text and won't sum. The fastest fixes are Find and Replace (replace "(" with "-" and ")" with nothing), Text to Columns with the trailing-minus option, or a formula that checks for parentheses and converts the rest with VALUE. For repeatable monthly work, do it in Power Query. Whatever you use, finish with a check: the column should be right-aligned numbers, COUNT should equal the row count, and the total should tie back to the statement.

Parentheses for negative numbers are an accounting convention. They're easy to read on paper and common on statements, ledgers and financial reports. In Excel, though, they're a frequent source of trouble. If you type (125) directly into a cell, Excel usually turns it into -125 automatically. But when the same thing arrives from a PDF, a CSV or a copy and paste, it often comes with extra characters, such as spaces, currency symbols, thousands separators or non-breaking spaces, that stop Excel from recognizing it as a number. The value sits in the cell as text, left-aligned, and quietly gets ignored by SUM.

This guide covers the common formats you'll see, how to detect the problem, five ways to fix it, how to deal with trailing minus signs and CR or DR markers, and how to display negatives in parentheses afterwards if you like the look.

The formats you're likely to see

Negative amounts in bank and accounting data commonly appear as:

A note on CR and DR: what counts as "negative" depends on the kind of account and whose point of view you take. On a bank statement for a deposit account, DR (debit) usually means money out and CR (credit) means money in. On a credit card statement, CR often marks a payment or refund that reduces what you owe. Check the statement's legend before deciding signs.

Why statements use parentheses at all

It helps to know why this convention exists, because it explains where you'll keep running into it. In accounting, a minus sign is small and easy to miss, especially on a printed page or a photocopy. Parentheses wrap the whole number, so a negative figure is hard to overlook. That's why financial statements, accountants' working papers and many system-generated reports use them.

Bank statements vary. Some print a leading minus, some use separate money-in and money-out columns with no signs at all, some add DR and CR markers, and some use parentheses for things like reversals or fees. Older core banking systems often produce a trailing minus, which is why Excel has a built-in option for it. Business account statements and reports exported from accounting software are particularly likely to use parentheses.

The practical point is that you can't assume one format per bank, or even per statement. A single file can mix several. That's why the formula approaches above check for each marker rather than relying on a single Find and Replace.

How to tell you have a problem

Signs that your amounts are text, not numbers:

• Left-aligned values in a column with default alignment. Numbers align right by default.
• A green triangle in the corner of cells, with a warning about numbers stored as text.
• SUM returns 0 or a total that's too small.
• COUNT is lower than COUNTA. COUNT counts numbers only. Try =COUNTA(B2:B500)-COUNT(B2:B500) to see how many cells in the range are text.

You can also check a single cell with =ISNUMBER(B2).

Method 1: Find and Replace

For a one-off fix, this is usually quickest.

1. Select the amount column.
2. Press Ctrl+H.
3. Find ( and replace with -. Click Replace All.
4. Find ) and replace with nothing. Click Replace All.
5. If there are currency symbols, find $ (or £, €) and replace with nothing.

Excel usually re-evaluates the cells and converts them to numbers. If some cells stay as text, there may be hidden characters; see the clean-up section below.

Watch out: only select the amount column. Running Replace All across the whole sheet will change parentheses in descriptions too, like "Transfer (savings)".

Method 2: a formula

A formula leaves the original data intact and works well when the data has a mix of formats. Put this in a helper column:

=IF(LEFT(TRIM(B2),1)="(", -VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"(",""),")",""),"$","")), VALUE(SUBSTITUTE(TRIM(B2),"$","")))

What it does:

• TRIM removes leading and trailing spaces.
• If the first character is (, it strips the parentheses and dollar sign, converts to a number with VALUE, and makes it negative.
• Otherwise it just strips the dollar sign and converts.

VALUE handles thousands separators that match your system's locale. If the column has blank cells, wrap it to avoid errors:

=IF(TRIM(B2)="", "", IF(LEFT(TRIM(B2),1)="(", -VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"(",""),")",""),"$","")), VALUE(SUBSTITUTE(TRIM(B2),"$",""))))

In Excel 365, LET makes it more readable:

=LET(t, TRIM(SUBSTITUTE(B2,"$","")),
     neg, LEFT(t,1)="(",
     n, VALUE(SUBSTITUTE(SUBSTITUTE(t,"(",""),")","")),
     IF(t="", "", IF(neg, -n, n)))

When you're happy with the result, copy the helper column and Paste Special as Values over the original, or keep both and hide the original.

Method 3: Text to Columns with trailing minus

Excel's Text to Columns has a setting specifically for trailing minus signs, which are common on older bank and system reports.

1. Select the amount column.
2. Go to Data, then Text to Columns.
3. Choose Delimited and click Next. Untick all delimiters and click Next.
4. Make sure the column format is General, then click Advanced.
5. Check the decimal and thousands separators, and tick Trailing minus for negative numbers.
6. Click OK, then Finish.

Values like 1,250.00- become -1,250. This method also handles parentheses in many cases, because Excel generally recognizes (1,250.00) as negative once it's re-parsed as General.

Method 4: a formula for trailing minus and CR or DR

If you'd rather use a formula, or you have CR and DR markers, try this. Assuming a deposit account where DR means money out:

=LET(t, UPPER(TRIM(SUBSTITUTE(SUBSTITUTE(B2,"$",""),CHAR(160)," "))),
     dr, RIGHT(t,2)="DR",
     cr, RIGHT(t,2)="CR",
     tm, RIGHT(t,1)="-",
     paren, LEFT(t,1)="(",
     core, TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,"DR",""),"CR",""),"(",""),")",""),"-","")),
     n, VALUE(core),
     IF(t="", "", IF(OR(dr, tm, paren, LEFT(t,1)="-"), -n, n)))

This removes currency symbols and non-breaking spaces, detects each negative marker, strips them, converts the rest and applies the sign. If your statement uses DR and CR the other way round (for example a credit card where CR is a payment), swap the logic for dr and cr to suit.

Method 5: Power Query for repeatable work

If you clean statements every month, do it once in Power Query and refresh each time.

1. Select your data and choose Data, From Table/Range.
2. Select the amount column. Use Transform, Replace Values to replace ( with - and ) with nothing. Repeat for currency symbols.
3. For trailing minus, add a custom column:

= if Text.EndsWith(Text.Trim([Amount]), "-")
  then -Number.From(Text.TrimEnd(Text.Trim([Amount]), "-"))
  else Number.From(Text.Trim([Amount]))

4. Set the column type to Decimal Number. If your data uses comma decimals, use Change Type, Using Locale.
5. Close and Load.

Next month, paste the new data into the source table and click Refresh. For a fuller Power Query workflow, see our Power Query bank statement guide.

Cleaning hidden characters

When a fix seems to work for most rows but not all, hidden characters are usually the cause.

• Non-breaking spaces (character 160) are common in data copied from PDFs and web pages. TRIM doesn't remove them. Use SUBSTITUTE(B2, CHAR(160), "").
• Other invisible characters can be removed with CLEAN, which strips many non-printing characters.
• Unicode minus signs or dashes. Some PDFs use an en dash or a special minus character instead of a hyphen. Find and Replace it with a normal hyphen. You can copy the odd character from a cell into the Find box.

A robust clean-up wrapper:

=TRIM(CLEAN(SUBSTITUTE(B2, CHAR(160), " ")))

Use that inside your conversion formula. Our guide to cleaning bank statement data in Excel covers many more of these issues.

Comma decimals and other locales

Statements from many countries use a comma as the decimal separator and a period or space for thousands, such as 1.250,00-. VALUE interprets text according to your system's locale, so it may misread these. Use NUMBERVALUE instead, which lets you specify the separators:

=NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")",""), ",", ".")

The second argument is the decimal separator and the third is the group (thousands) separator. Combine it with the sign logic above. NUMBERVALUE also treats a trailing minus as negative in many cases, but test on your own data.

Worked example: a statement with mixed formats

Suppose you've converted a statement and the Amount column contains:

Using the Method 4 formula in column C:

1. Enter the formula in C2 and fill down.
2. Check: =SUMPRODUCT(--ISNUMBER(C2:C7)) should return 6.
3. Check the total: =SUM(C2:C7) gives 2,391.37.
4. Compare with the statement: opening balance plus 2,391.37 should equal the closing balance.

That last step is the one that really matters. A sign error doesn't stop the column being numeric, so ISNUMBER alone won't catch it. Tying back to the statement's balances will. Our guide to the running balance formula in Excel shows how to check line by line.

When the sign is in a separate column

Some statements don't put the sign on the amount at all. Instead, there's a separate column with a code such as D or C, DR or CR, a plus or minus, or a transaction type like "DEBIT" and "CREDIT". After conversion you might have:

In that case, convert the amount to a number first, then apply the sign from the type column:

=IF(UPPER(TRIM(C2))="DR", -B2, B2)

If the codes vary, for example "D", "DR" and "DEBIT", test the first letter instead:

=IF(LEFT(UPPER(TRIM(C2)),1)="D", -B2, B2)

As always, check the result against the statement's opening and closing balances before you trust it.

The same fixes in Google Sheets

If you work in Google Sheets, most of the same ideas apply, with a few differences:

• Find and replace works the same way (Ctrl+H). Limit it to the amount column with the "Specific range" option.
• VALUE and SUBSTITUTE behave much like Excel, so the Method 2 formula works with little or no change.
• REGEXREPLACE is handy for stripping everything except digits, the decimal point and a minus sign in one step:

=IF(REGEXMATCH(B2,"\(|-$|DR"), -1, 1) * VALUE(REGEXREPLACE(B2, "[^0-9.]", ""))

That formula treats parentheses, a trailing minus or a DR marker as negative and strips everything else. Adjust it for your own markers and for comma decimals if needed.

• Number formatting for parentheses is under Format, Number, Custom number format, using the same #,##0.00;(#,##0.00) pattern.

For a broader workflow, see our guide to converting a bank statement PDF to Google Sheets.

Checking a whole year at once

When you've converted twelve statements, check each one rather than the year as a whole. A sign error in March and a missing row in August can offset each other in an annual total and hide both problems. Build a small summary table with one row per statement: opening balance, sum of amounts for that statement (using SUMIFS on a Statement column), closing balance, and the difference. Every difference should be zero. Our guide to combining multiple bank statements in Excel shows how to set this up.

Splitting into debit and credit columns

Sometimes you want the reverse: a signed column split into separate debit and credit columns, for example for an accounting import. Once your amounts are real numbers:

Debit:  =IF(C2<0, -C2, "")
Credit: =IF(C2>0, C2, "")

Our guide to separating debit and credit columns covers this in both directions.

Displaying negatives in parentheses

Once your values are real numbers, you can still show negatives in parentheses without turning them back into text. Use number formatting:

1. Select the column.
2. Press Ctrl+1 to open Format Cells.
3. Choose Custom and enter:

#,##0.00;(#,##0.00);0.00

The three sections are positive; negative; zero. To show negatives in red as well:

#,##0.00;[Red](#,##0.00);0.00

The Accounting and Number formats also have built-in options for parentheses, depending on your locale. Formatting changes only the display, so formulas and totals keep working.

Preventing the problem

You can't control how statements are printed, but you can reduce how often you fix this by hand:

• Use a converter that outputs signed numbers. SheetStatement extracts transactions from PDF statements into a single signed Amount column and checks the statement balance before export. The free plan includes 3 pages a month; Starter ($9), Pro ($19) and Business ($39) monthly plans cover more.
• Build a reusable Power Query for each source format you deal with regularly.
• Keep a template with conversion formulas already in place, so you paste raw data into one column and get clean numbers in the next.

Common mistakes

Replacing parentheses across the whole sheet. Descriptions often contain parentheses too. Select only the amount column.

Assuming every parenthesized value is negative. On some reports, parentheses mean something else, such as a note reference. Check the source.

Ignoring CR and DR meaning. Credits and debits mean different things for different accounts. Read the statement legend.

Not checking the total. A column full of numbers can still have the wrong signs. Always tie back to the statement's balances.

Converting dates by accident. Some text-to-number fixes can turn values like 1-2 into dates. Only run conversions on the amount column.

Clean data from the start

Upload your bank statement PDFs to SheetStatement, check the balance, and download Excel or CSV with real signed numbers. Then use the techniques above for any other reports that still arrive with parentheses.

FAQ

Why doesn't Excel recognize (1,250.00) as a negative number?

Usually because the cell contains extra characters, such as spaces, non-breaking spaces or currency symbols, or because it was imported as text. Clean the text, then convert it with Find and Replace, Text to Columns or a formula.

How do I convert a trailing minus like 125.00- in Excel?

Use Data, Text to Columns, click Advanced and tick "Trailing minus for negative numbers". Or use a formula that checks RIGHT(A2,1)="-" and negates the rest.

How do I handle CR and DR on bank statements?

Check the statement's legend. On a deposit account, DR usually means money out and CR money in. Strip the marker and apply the sign with a formula.

Can I show negatives in parentheses without breaking formulas?

Yes. Use a custom number format such as #,##0.00;(#,##0.00). The value stays numeric and only the display changes.

Why do some cells still show as text after Find and Replace?

They probably contain non-breaking spaces or other hidden characters. Use SUBSTITUTE(A2,CHAR(160),"") and CLEAN before converting.

How do I make sure the signs are right?

Check that the opening balance plus the sum of amounts equals the closing balance on the statement. Being numeric doesn't prove the signs are correct.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.