# 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.