Business

Why Pound Signs Turn Into Strange Characters in a CSV Opened in Excel

Fix a CSV where the pound sign shows up as odd symbols or the columns collapse in Excel, with a clear encoding explanation and a step-by-step repair.

Editorial illustration for Why Pound Signs Turn Into Strange Characters in a CSV Opened in Excel

If your spending CSV shows £ as a pair of odd characters, you are looking at an encoding mix-up: the file was saved in one way and Excel read it in another. The repair takes a minute. Import the file through the Data tab and set the file origin to UTF-8, instead of opening it by double-click.

What is actually happening to the pound sign?

Text is stored as numbers, and different encodings assign different numbers to the same character. A file saved in UTF-8 stores the pound sign using two bytes. If a program assumes an older single-byte style, it shows those two bytes as two separate characters, which is why you see a capital A with a circumflex followed by a pound sign. Nothing is lost in the file itself; only the reading is wrong.

  • The file data is fine; do not retype it
  • Only the interpretation changes, so re-importing fixes it
  • Saving the garbled view back over the file makes the damage permanent

How do you repair it in a few clicks?

  1. Close the file without saving.
  2. Open a new blank workbook.
  3. Choose Data, then From Text/CSV, and select your file.
  4. In the preview, set File Origin to 65001: Unicode (UTF-8).
  5. Confirm the pound signs read properly and the columns separate.
  6. Click Load.

If the columns are crammed into one, change the delimiter in the same window until the preview looks tidy.

Can the amounts be wrong as well?

They can be hard to use. If amounts keep the pound sign inside the cell, such as £12.40, Excel may treat them as text and refuse to add them. A quick test is to select the column and look at the status bar sum. If it shows zero, they are text. Use Find and Replace to remove the symbol, or use this formula in a helper column: =VALUE(SUBSTITUTE(B2,"£","")). Fill it down and then paste as values.

RowOriginal textAfter VALUE and SUBSTITUTE
Rent share£420.00420
Train ticket£32.5032.5
Takeaway£18.7518.75

The three numbers add to 420 + 32.5 + 18.75 = 471.25, which is what the column should show once the amounts are real numbers.

What should you do about exports you receive from others?

Ask the sender to confirm the format, or tell them yours is UTF-8 with comma separators. Many problems disappear when both sides state the format. If you export from a tracker yourself, check the first few rows before sending the file on.

A tidy CSV from the start

A tracker that outputs plain, consistent columns reduces repair work. SpendixAI is built around India and INR rather than pound accounts, but its CSV downloads follow the same principle: one transaction per row and plain headers.

Why does the problem come and go?

It often appears only on some files because only some contain characters outside plain English letters. A month with no pound signs in descriptions, only in a separate amount column the program handles, may look fine, while the next month with a symbol in a merchant name shows the problem. That makes it feel random. It is not random: any file with non-basic characters can trip it, so apply the UTF-8 import step to every CSV before you trust it, rather than only to the ones that look wrong.

Quick checklist

  • Never double-click a CSV that looks wrong
  • Import with UTF-8 origin
  • Remove symbols from amounts
  • Test one date you know
  • Keep a clean original
SPENDIXAI JOURNAL

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

Explore your reports