CR and DR amounts in Excel are text, not numbers, because the letters sit inside the cell. Split the suffix into its own column, convert what remains to a number, then apply a sign: on a bank account, CR is usually money in and DR money out. Prove the rule by rebuilding the running balance from the result.
What CR and DR mean beside a statement amount
CR stands for credit and DR for debit. On a checking or savings statement, a credit adds to your balance and a debit takes from it, so CR marks money in and DR marks money out. Some statements print both letters, and others print only CR and leave every unmarked amount as a debit.
The letters describe the effect on the account from the bank's point of view, which is where confusion starts. To the bank, your deposit is money it owes you, a liability, so it records your deposit as a credit. That is why a bank statement credit is good news for you, while a credit in your own books can mean something else.
For the sheet, only one question matters: which direction does each letter move the balance? Answer it once for each statement layout, write it down beside the data, and the rest is mechanical.
Converted files keep the letters because the PDF printed them as part of the amount, such as "1,250.00 CR" or "62.18DR". Excel sees letters in the cell and stores the whole thing as text. SUM ignores it, and sorting by amount puts rows in character order rather than size order.
Splitting the CR or DR suffix into its own column
Splitting the suffix gives you two clean columns: the number and the marker. The simplest formula takes the last two characters as the marker and everything before them as the amount. Use a helper column for each, check a sample, then paste values when they look right.
With the raw text in B2:
- Marker:
=UPPER(RIGHT(TRIM(B2),2)) - Amount:
=VALUE(TRIM(LEFT(TRIM(B2),LEN(TRIM(B2))-2)))
TRIM matters in both. A converted cell often ends with a trailing space, and without TRIM the RIGHT function returns "R " instead of "CR". UPPER catches statements that print "Cr" or "cr".
Rows with no marker need a guard. If the statement leaves debits unmarked, the amount formula above would chop the last two digits off "62.18". Wrap it so it only trims when a marker is present:
=IF(OR(RIGHT(TRIM(B2),2)="CR",RIGHT(TRIM(B2),2)="DR"),VALUE(TRIM(LEFT(TRIM(B2),LEN(TRIM(B2))-2))),VALUE(TRIM(B2)))
The comparisons in Excel are not case-sensitive, so "cr" also matches.
Using Text to Columns instead
If every cell has a space before the marker, Text to Columns does the split without formulas. Select the column, choose Data, Text to Columns, pick Delimited, tick Space, and finish. The amount lands in one column and the marker in the next. Insert an empty column to the right first, or the marker overwrites whatever sits there.
A formula that turns CR and DR amounts into signed numbers
One formula can do the whole job: strip the marker, convert the number, and apply the sign. With the raw text in B2 and the bank-account rule of CR positive and DR negative, the formula multiplies the cleaned amount by minus one whenever the cell ends in DR.
=LET(t,TRIM(B2),m,UPPER(RIGHT(t,2)),n,IF(OR(m="CR",m="DR"),VALUE(TRIM(LEFT(t,LEN(t)-2))),VALUE(t)),IF(m="DR",-n,n))
LET names each step so the formula reads in order: t is the trimmed text, m the marker, n the number, and the last part applies the sign. LET needs Excel 365 or Excel 2021. In older versions, use the split columns from the previous section and a third column: =IF(C2="DR",-D2,D2).
If your statement marks only credits and leaves debits bare, change the last part so unmarked rows go negative: IF(m="CR",n,-n).
Comma thousands separators
VALUE reads "1,250.00" correctly when your regional settings use a comma for thousands. If your system uses a comma as the decimal mark, the same text fails. NUMBERVALUE with explicit separators avoids that: replace each VALUE(...) with NUMBERVALUE(..., ".", ",").
Which CR and DR sign rule fits your statement?
The sign rule depends on whose balance the statement tracks. On a bank account, CR adds to your money, so it takes a plus sign. On a credit card or loan statement, CR reduces what you owe, which is good for you but makes the balance smaller, so the rule depends on what you want to measure.
Here are the common cases:
| Statement | CR usually means | DR usually means | Sign rule for spending analysis |
|---|---|---|---|
| Checking or savings | Deposit, refund, interest | Payment, withdrawal, fee | CR plus, DR minus |
| Credit card | Payment or refund | Purchase, fee, interest | CR plus, DR minus |
| Credit card, tracking amount owed | Payment lowers the balance | Purchase raises the balance | CR minus, DR plus |
| Loan account | Repayment | Interest or new borrowing | Depends on whether you track cash or debt |
These are typical patterns, not rules every bank follows. Read the statement's own legend if it has one, and test the rule against the balance column before you use it for anything.
For spending analysis, CR plus and DR minus works on both bank and card statements, because a card purchase is money you spent and a card payment is money you moved. The table's third row is for the other question, how the debt changes.
Balance columns marked CR or DR
A running balance can carry CR or DR too. On a bank account, a balance marked CR is in credit, meaning the account holds money, and a balance marked DR is overdrawn. On a card, a CR balance means the card company owes you, usually after a refund larger than what you owed.
Convert the balance column with the same formula as the amounts, so an overdrawn balance becomes negative. Without that step, an overdrawn "250.00 DR" reads as plus 250 once you strip the letters, and your running check fails on every row after the account dips below zero.
This is also where an unmarked balance can mislead. Some statements print DR beside an overdrawn balance and nothing beside a positive one. The guarded formula handles that, since unmarked values stay positive.
Once overdrawn balances carry a minus sign, a column that was just wide enough can fill with ### signs. That is only a display problem; fixing Excel cells that show hashtags instead of numbers covers it.
CR and DR markers that landed in the wrong cell
Converters sometimes separate the marker from its amount. The letters end up in the next column, at the end of the description, or alone on the row below. Each case needs a small fix before the sign formula can work, and the running balance tells you when one has been missed.
The three patterns and their fixes:
- Marker in the next column. The amount converts cleanly and the next column holds "CR" or "DR". Point the sign formula at that column:
=IF(TRIM(D2)="DR",-C2,C2). - Marker at the end of the description. A description reads "PAYROLL ACME CR". Pull the marker with
=RIGHT(TRIM(A2),2)and strip it from the description with LEFT and LEN, checking that real descriptions ending in those letters are not caught. - Marker alone on the next row. The PDF wrapped the marker onto its own line, so the row below holds "CR" and nothing else. Use
=IF(TRIM(B3)="CR",...)against the row below, then delete the orphan rows.
Orphan rows often leave the date column blank as well, and other blank dates come from statements that print the date only once per day. Filling down blank dates in Excel handles that before you sort.
If the marker problems repeat every month, the same split and sign steps are easier to keep in Power Query, where they rerun on each new file. Cleaning PDF data with Power Query transform steps shows how.
Does the CR and DR sign rule match the running balance?
The running balance proves the sign rule. Start from the opening balance, add each signed amount in order, and compare the result with the balance printed on each row. If every row matches, the rule is right. If the first mismatch is off by twice the amount, that row's sign is flipped.
A worked example
The rows below are made up. The opening balance is 500.00 CR.
| Row | Raw amount | Marker | Signed amount | Printed balance | Signed balance | Running check | Match? |
|---|---|---|---|---|---|---|---|
| 2 | 120.00 DR | DR | -120.00 | 380.00 CR | 380.00 | 380.00 | Yes |
| 3 | 1,250.00 CR | CR | 1,250.00 | 1,630.00 CR | 1,630.00 | 1,630.00 | Yes |
| 4 | 1,800.00 DR | DR | -1,800.00 | 170.00 DR | -170.00 | -170.00 | Yes |
| 5 | 45.50 DR | DR | -45.50 | 215.50 DR | -215.50 | -215.50 | Yes |
| 6 | 300.00 CR | CR | 300.00 | 84.50 CR | 84.50 | 84.50 | Yes |
The running check in row 2 is =500+D2, and from row 3 on it is the cell above plus this row's signed amount. The match column compares the check with the signed balance, rounded to two places. Rows 4 and 5 show why the balance column needs converting too: printed as "170.00 DR", the overdrawn balance only matches once it becomes -170.00.
Working on a Chromebook or in a browser
LET, NUMBERVALUE and the other functions here also work in Excel for the web, so the same steps run on a machine without desktop Excel. Opening Excel files on a Chromebook covers the options there.
FastBankConvert is getting ready to open. It is built to put amounts and balances in their own columns and to tell you before you download if the balances do not add up; Get started adds you to the list.