Spreadsheets

Convert amounts like "1,234.50 Dr" into numbers in a sheet

Convert bank amounts like "1,234.50 Dr" into real numbers with SUBSTITUTE, VALUE and a sign flip so debits are negative, in Excel and Google Sheets.

Editorial illustration for Convert amounts like "1,234.50 Dr" into numbers in a sheet

When a statement amount reads "1,234.50 Dr", the sheet treats it as text, so it will not add up. Strip the commas and the Dr or Cr letters with SUBSTITUTE, convert what is left with VALUE, and multiply by minus one when the suffix says Dr. That gives a true number with debits as negatives.

Why will my sheet not add these amounts?

Text hides in plain sight. A cell that reads 1,234.50 Dr is a string of characters, left-aligned by default, and SUM ignores it. The commas, the letters, a trailing space, or a non-breaking space copied from a PDF can all cause it. Try =ISNUMBER(A2): FALSE confirms it is text.

A quick visual test: select the whole amount column and look at the status bar at the bottom of Excel. If it shows only a Count and no Sum, the cells are text. Google Sheets shows a similar summary at the bottom right when you select cells, so compare what it offers for a column of text and for a column of numbers.

What is the formula, step by step?

Use two helper columns. Put the raw text in A. In B, clean the spaces, including the non-breaking ones that PDFs often carry:

  • B2: =TRIM(SUBSTITUTE(A2,CHAR(160)," "))

In C, work out the number and the sign:

  • C2: =IF(UPPER(RIGHT(B2,2))="DR",-1,1)*VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(B2),",",""),"DR",""),"CR","")))

Here is how it flows. UPPER makes DR and Dr and dr alike, since SUBSTITUTE is case-sensitive. The three SUBSTITUTE calls strip commas, then DR, then CR. TRIM removes the space left behind. VALUE turns the remaining text into a number. The IF in front reads the last two letters and multiplies by -1 for Dr. A Cr amount stays positive.

Raw textResultReason
1,234.50 Dr-1234.5Dr flips the sign
20,000.00 Cr20000Cr stays positive
75.00Dr-75No space before Dr is fine
1,05,000.25 Dr-105000.25Indian-style commas also removed

How do I do the whole column at once in Google Sheets?

Sheets can fill a column from one cell with ARRAYFORMULA. Put this in C2, and make sure C3 below is empty:

  • =ARRAYFORMULA(IF(A2:A="","",IF(UPPER(RIGHT(TRIM(SUBSTITUTE(A2:A,CHAR(160)," ")),2))="DR",-1,1)*VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(SUBSTITUTE(A2:A,CHAR(160)," "))),",",""),"DR",""),"CR","")))))

It is long because Sheets needs the clean-up inside it. Excel 365 users can shorten the same logic using LET, which lets you name the cleaned text once. LET is not in Excel 2016 or earlier.

What if some amounts are already negative or in brackets?

Statements are not consistent. Some write a minus sign, some write brackets, some write the amount in one column for debits and another for credits. A minus sign is handled by VALUE without help. Brackets, such as (500.00), need SUBSTITUTE for "(" and ")" and a sign flip. When debit and credit sit in two columns, you do not need any of this: use =D2-C2 and treat debits as positive spending.

There is a no-formula route if you only have a few rows. Filter the column for entries ending in Dr, use Find and Replace (Ctrl+H) to remove Dr and the commas, and then multiply the visible numbers by -1 using Paste Special, Multiply. It is fiddly, and a formula is easier to repeat next month. Also note that VALUE depends on regional settings: a computer set to use a comma as the decimal mark may reject 1234.50, so test one row first.

How do I check the result?

  1. Total the converted column and compare debits and credits with the totals printed on the statement.
  2. Count rows: =COUNTA(A2:A500) should equal =COUNT(C2:C500). Any difference is a row that failed.
  3. Filter column C for errors; #VALUE! usually points to a stray letter such as "CR." with a full stop.
  4. Copy column C and paste as values (Ctrl+Shift+V) before deleting the helpers.

If you handle statements every month, SpendixAI takes PDF, CSV or Excel statement files and lets you review each row before saving, which avoids this clean-up entirely, though it is worth keeping the formula for odd exports.

SPENDIXAI JOURNAL

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

Explore your reports