Negative numbers in parentheses in Google Sheets are often text, not numbers, when they come from a PDF. Convert them by swapping the opening parenthesis for a minus sign and dropping the closing one, with a formula or Find and replace. To show real negatives in parentheses, apply a custom number format such as #,##0.00;(#,##0.00).
Why do parenthesized negatives arrive as text in Google Sheets?
Parenthesized negatives arrive as text in Google Sheets because the parentheses, commas and currency signs copied from a PDF are characters, not formatting. A PDF report prints (1,250.00) as ten characters on a page. When those characters reach a cell, Sheets has to decide whether the string is a number, and with extra symbols it often decides it is not.
Accounting statements, profit and loss reports and some bank and loan statements use parentheses for negatives because the convention is easy to read in print. A minus sign is a small mark that can be missed, while brackets around a number stand out. The convention is a display choice. The number underneath is simply negative.
In a spreadsheet the display and the value are separate. A cell can hold the value -1250 and show it as (1,250.00). Text copied from a PDF has lost that separation: what arrives is only the displayed string. Whether Sheets turns it back into a number depends on the exact characters and on the spreadsheet's locale.
You can tell which kind of cell you have with three quick checks:
- Alignment. By default, numbers sit on the right of a cell and text on the left. A column of left-aligned amounts is text.
- The status bar. Select the column. If the bottom-right corner shows only a count and no sum, the values are text.
- ISNUMBER. In a spare column, =ISNUMBER(A2) returns FALSE for a text amount.
If the parenthesized cells already sum correctly, Sheets has read them as numbers and you can skip ahead to the formatting section.
Converting parenthesized amounts to negative numbers with a formula
A formula converts parenthesized amounts to negative numbers by rewriting the text into a form Sheets can read, then turning it into a value. Replace the opening parenthesis with a minus sign, remove the closing parenthesis and the thousands commas, and wrap the result in VALUE. The original column stays as it was.
With an example text amount in A2, this formula returns a real number:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, ",", ""), "(", "-"), ")", ""))
Read from the inside out, it removes commas, turns "(" into "-", removes ")", and converts what is left. A positive amount such as 980.00 has no parentheses, so it passes through unchanged and VALUE turns it into 980.
A worked example of raw amounts
The amounts below are examples of what a pasted column might hold.
| Example raw cell | After the formula | Shown with #,##0.00;(#,##0.00) |
|---|---|---|
| (1,250.00) | -1250 | (1,250.00) |
| 980.00 | 980 | 980.00 |
| (4.75) | -4.75 | (4.75) |
| 12,400.10 | 12400.1 | 12,400.10 |
| ($38.20) | Error | Needs the currency sign removed first |
The last row shows the limit of the simple formula: any character it does not strip will stop VALUE. The edge cases section near the end covers currency signs, suffixes and spaces.
Once the helper column is correct, copy it, then use Edit, then Paste special, then Values only, over the original column. Delete the helper. The amounts are now numbers, and the formula is gone so nothing breaks if the source column changes.
Check the total against the report before you move on. Compare the sum of the converted column with a total printed on the PDF. If the report shows net expenses of (4,312.60) and your sum is -4312.6, the conversion is complete. A sum that is too large usually means a few negatives slipped through as positives.
Find and replace for parenthesized negatives across a Google Sheets column
Find and replace fixes parenthesized negatives across a Google Sheets column in place, without a helper column. Select the amount column, open Edit, then Find and replace, and run two replacements: "(" with "-", then ")" with nothing. Sheets re-reads each edited cell, and amounts like -1250.00 usually become numbers.
Work on the selected range only. In the Find and replace dialog, set Search to the specific range rather than all sheets, or you may change parentheses inside descriptions and notes elsewhere in the file. A description such as "TRANSFER (SAVINGS)" would otherwise become "TRANSFER -SAVINGS".
If you are comfortable with patterns, the dialog also has a regular expression option. A single replacement can target only cells that are wholly wrapped in parentheses:
Find: ^\((.*)\)$
Replace: -$1
That leaves a parenthesis in the middle of a cell alone and only rewrites cells that start with "(" and end with ")".
After the replacement, check the column with ISNUMBER or the status bar sum. Cells that are still text usually hold another character, often a thousands comma in a locale that expects a full stop, or a hidden space. Those need the formula route.
A custom number format that shows negatives in parentheses
A custom number format shows real negative numbers in parentheses without changing their value. The cell still holds -1250, so it sums and sorts correctly, but it displays as (1,250.00). Google's help page on formatting numbers in a spreadsheet explains that a format can have up to four parts separated by semicolons.
The four parts are, in order, the format for positive numbers, negative numbers, zero and non-numeric values. For accounting-style display you need only the first two:
#,##0.00;(#,##0.00)
The steps, from the same Google page:
- Select the cells to format.
- Click Format, then Number, then Custom number format.
- Type the format into the box, or pick a similar one from the list and edit it.
- Click Apply.
The page also notes that you can add a colour in brackets, such as [Red]. A format of #,##0.00;Red shows negatives in red with parentheses, which is common on financial reports.
Two variations come up often:
- A zero shown as a dash. Add a third section: #,##0.00;(#,##0.00);"-". Zero balances then show a dash instead of 0.00.
- Aligned positives. In print, accountants often leave a space after a positive number so its digits line up with negatives in brackets. Adding a trailing space to the first section, as in #,##0.00 ;(#,##0.00), gives a similar effect.
Remember that the format affects display only. A formula that refers to the cell sees -1250, not the parentheses.
Do parentheses survive when the sheet is exported to Excel?
Parentheses survive an export to Excel when they come from a number format, because the format travels with the XLSX file. A cell holding -1250 with the format #,##0.00;(#,##0.00) opens in Excel as the same number with the same display. Parentheses typed as text also survive, but stay text.
That is why converting first matters. An amount that is still text, such as "(1,250.00)", will be text in Excel as well, and Excel's own totals will skip it. Downloading a Google Sheet as an Excel file walks through the export and what else carries over.
A CSV is different, because a CSV stores no formatting at all. Each cell becomes plain characters in a line of text. Depending on how the CSV is produced, a formatted negative may be written as -1250 or as the displayed string. Open the CSV in a text editor and look before you send it to a budgeting app or an import tool that expects a minus sign.
The reverse trip works the same way. When you upload an Excel file to Google Sheets, real numbers keep their custom formats, and amounts the Excel file held as text arrive as text. Fix them once, in whichever app you work in, and the format will follow the file.
Amounts with currency signs, CR suffixes or spaces inside the parentheses
Currency signs, CR suffixes and spaces inside the parentheses are the edge cases that defeat the simple formula. Each adds a character that VALUE cannot read. The fix is to strip every non-digit except the minus sign and decimal point, and to handle suffixes as their own sign marker.
A more general formula uses REGEXREPLACE to keep only digits, the decimal point and the minus sign, after turning "(" into "-":
=VALUE(REGEXREPLACE(SUBSTITUTE(A2, "(", "-"), "[^0-9.\-]", ""))
It handles ($38.20), ( 4.75 ) and (1,250.00 USD) alike. It does not know about decimal commas, so check the next point if your reports use them.
Other cases to watch for:
- Decimal commas. A European report may print (1.250,00). The formula above would read it as -1.25. Swap the separators first, or set the spreadsheet locale under File, then Settings, to match the report before you convert.
- CR and DR suffixes. Some statements print 1,250.00 CR for a credit and 1,250.00 DR for a debit, with no parentheses. Which one is negative depends on whose point of view the statement takes. Decide the sign rule once, then use IF with RIGHT to apply it.
- Trailing minus signs. Values such as 1,250.00- need the minus moved to the front. REGEXREPLACE can do that with a pattern that captures the number and the trailing minus.
- Parentheses used for notes. A cell like "45.00 (est.)" is not a negative. Filter for cells that both start with "(" and end with ")" before converting, so notes are not flipped.
Copying text out of a PDF loses more than number formats. Hyperlink targets on a statement or report are dropped too, which extracting hyperlinks from a PDF to Excel explains how to recover. For statements, FastBankConvert is being built to return dates, descriptions, amounts and balances in separate columns as Excel or CSV, and to flag a statement whose balances do not add up; the homepage describes the plan.