Spreadsheets

Split amounts like 1,200.00 Dr into a number and a Dr/Cr flag

Split a statement amount like 1,200.00 Dr into a number and a Dr/Cr flag with LEFT, RIGHT and TRIM, with or without a space, then a signed column.

Editorial illustration for Split amounts like 1,200.00 Dr into a number and a Dr/Cr flag

When an amount arrives as 1,200.00 Dr, put the flag in one column with =UPPER(RIGHT(TRIM(C2),2)) and the figure in another with =VALUE(SUBSTITUTE(TRIM(LEFT(TRIM(C2),LEN(TRIM(C2))-2)),",","")). Because TRIM runs again after LEFT, the pair works whether or not there is a space before Dr or Cr.

What does the cell actually contain?

Some statements print the suffix as a separate word, "1,200.00 Dr", and others glue it on, "1,200.00Dr". To Excel both are plain text, so SUM ignores them and subtraction fails. The trick is that Dr and Cr are always two characters long, so you can peel them off the right-hand end and keep what is left.

Cell C2Flag in D2Number in E2
1,200.00 DrDR1200
1,200.00DrDR1200
45,000.00 CrCR45000
310.50 DRDR310.5

How do you build the three columns?

Set up the columns in this order and fill each one down.

  1. Flag in D2: =UPPER(RIGHT(TRIM(C2),2)). RIGHT takes the last two characters, TRIM removes stray spaces first, and UPPER means dr, Dr and DR all behave the same.
  2. Number in E2: =VALUE(SUBSTITUTE(TRIM(LEFT(TRIM(C2),LEN(TRIM(C2))-2)),",","")). LEFT keeps everything except the last two characters, the outer TRIM clears the space before Dr, SUBSTITUTE removes commas and VALUE makes it a number.
  3. Signed amount in F2: =IF(D2="DR",-E2,E2). Money out becomes negative, money in stays positive.

On most bank-account statements Dr is money leaving your account and Cr is money arriving, but confirm with a payment you remember. Card statements can follow a different habit, so look at your own layout before trusting the sign.

What if some amounts have no suffix at all?

Opening-balance rows and blank lines sometimes lack a Dr or Cr. The formula above would then chop two digits off a real figure. Guard it:

  • =IF(OR(D2="DR",D2="CR"),VALUE(SUBSTITUTE(TRIM(LEFT(TRIM(C2),LEN(TRIM(C2))-2)),",","")),"") returns a number only when a flag exists.
  • =IF(C2="","",UPPER(RIGHT(TRIM(C2),2))) keeps the flag column tidy on empty rows.

Rows left blank by the guard are exactly the ones to look at by eye.

Is Text to Columns a faster way?

It can be, if your suffix always has a space before it. Select the amount column, go to Data, Text to Columns, choose Delimited, tick Space as the delimiter, and Excel will put 1,200.00 in one column and Dr in the next. The weakness is the glued version, 1,200.00Dr, which has no space to split on, and values with a space inside such as "1 200.00 Dr" would split into three pieces. The formula route handles both, and you can fill it down again next month.

Worked example with hypothetical amounts

Say three rows read 18,000.00 Dr for rent, 2,350.00 Dr for groceries and 52,000.00 Cr for salary. The signed column gives -18000, -2350 and 52000. Their sum is 31,650, which is what the account gained over those three lines. If your sheet shows anything else, a flag has gone wrong somewhere.

Does this need a newer version of Excel?

No. LEFT, RIGHT, TRIM, SUBSTITUTE, LEN and VALUE exist in every version of Excel and in Google Sheets, so the formulas above need nothing newer. If your bank prints Dr. with a full stop, or spells out Debit, the suffix is longer: change both 2s to the new length and compare against that text instead.

Once the signed column exists, a filter on the flag column plus =SUBTOTAL(109,F2:F500) totals only the visible rows, so you can see all Dr lines or all Cr lines in a second.

Before you rely on it

  • Keep the original text column; hide it if the sheet looks busy.
  • Add the check column and confirm it reads 0.
  • Compare SUM of the signed column with closing balance minus opening balance.
  • Format the signed column with two decimals.

If statements arrive every month and you would rather not rebuild this, SpendixAI takes PDF, CSV or Excel statements and shows the rows for review before saving; layouts vary, so it is worth scanning the rows it shows.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports