How to Clean Amounts Pasted from a Bank or Payment Export

Written with AI assistance. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).

You paste a statement or a payment-platform export into your sheet, and the SUM at the bottom is wrong. Or worse, it looks right. Usually some "numbers" aren't numbers at all: 1,250.00 arrived as text, a ₹ came along for the ride, there's an invisible non-breaking space, or a refund is written (300). SUM skips text silently.

Here's a cleaning column, a signed-amount column, a flag column and one reconciliation check. It needs no macros and no Power Query.

1. Layout

Paste the export as it comes, starting at row 5:

Column	What goes in it
A	Reference
B	Amount, as pasted
C	Dr / Cr (if your export has it)
D	Cleaned text (formula)
E	Amount (formula)
F	Signed amount (formula)
G	Flag (formula)

Type the statement total from the bank or platform into B2.

2. Strip the junk (D5)

=IF(B5="","",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,UNICHAR(160),"")," ",""),",",""),"₹",""),"$",""))

From the inside out, this removes:

• non-breaking spaces (UNICHAR(160)), which TRIM does not remove and which are common in web and PDF exports
• ordinary spaces, so 1 100 becomes 1100
• thousands commas
• the ₹ and $ symbols

Add another SUBSTITUTE(…,"€","") layer if you need it. UNICHAR needs Excel 2013 or later; Google Sheets has it too.

This assumes the export uses a dot for decimals. If yours writes 1.250,00, swap the roles of the comma and dot first.

3. Turn it into a number (E5)

=IF(D5="","",IFERROR(IF(LEFT(D5,1)="(",-VALUE(MID(D5,2,LEN(D5)-2)),VALUE(D5)),"Fix"))

• (300) is accounting notation for minus 300, so a leading bracket becomes a negative number.
• Anything VALUE still can't read shows the word Fix. It doesn't become a quiet zero. In my sample, 12O (a letter O typed instead of a zero) landed here.

Using IFERROR here is fine because it returns a visible label that's counted below. Hiding errors as 0 is the habit to avoid.

4. Apply Dr / Cr (F5)

=IF(OR(E5="",E5="Fix"),"",IF(UPPER(TRIM(C5))="DR",-ABS(E5),E5))

Debits become negative, credits stay as they are. UPPER and TRIM mean cr, Cr and CR all count as the same thing. If your export has no Dr/Cr column, use =E5 here instead.

5. Flag what needs a human (G5)

=IF(B5="","",IF(E5="Fix","Fix amount",IF(OR(UPPER(TRIM(C5))="DR",UPPER(TRIM(C5))="CR"),"","Check Dr/Cr")))

(If you don't have a Dr/Cr column, keep only the "Fix amount" part.)

6. Reconcile against the statement

Cell	Formula
Rows to fix (J5)	=COUNTIF($G$5:$G$104,"Fix amount")+COUNTIF($G$5:$G$104,"Check Dr/Cr")
Cleaned total (J6)	=SUM($F$5:$F$104)
Statement total (J7)	=$B$2
Difference (J8)	=ROUND(J6-J7,2)
Check (J9)	=IF(AND(J5=0,J8=0),"OK","CHECK")

The check only says OK when every row is readable and your total matches the bank's to the paisa or cent. In my ten sample rows:

• 1 row needed fixing (the 12O)
• the cleaned total was 5,370.50 against a typed statement total of 3,420.50, so the check said CHECK

That's what it should do. Both numbers in the sample are made up; the point is that a mismatch can't hide.

7. Test it before you trust it

Plant one of each, and confirm what you see:

Pasted value	Expected amount
1,250.00	1250
₹2,000	2000
450.50 (non-breaking spaces)	450.5
(300)	-300
250 with Dr	-250
12O (letter O)	Fix
a blank row	stays blank

Then delete the planted rows. Next time, paste the new export into column B and the columns to the right do the rest.

---

Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker

About me, and a disclosure: I'm Ashish, based in Kolkata, with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.