Bank export, about 12,000 rows. Amounts came in as text with thousands separators, and SUM ignores the column entirely. Multiplying by 1 gives VALUE errors on some rows. There is also something odd where negatives appear as 1.234,56- with the sign at the end. What is the actual sequence to clean this up properly?
Before anything else, check whether the source can export differently. Ten minutes asking for a plain CSV with a period decimal and a leading minus beats an hour of cleaning, and it fixes every future export too.
This is the correct answer to most data cleaning questions and the one nobody wants to hear.
If you must do it in place: select the column, Data, Text to Columns, next, next, then on step three click Advanced and set the thousands and decimal separators explicitly. It handles the separator half instantly.
Check for a currency symbol or a three letter code inside the cell as well. One stray symbol on 40 rows gives you exactly the VALUE errors on a subset that you described.
@xlookup_lena · 4mo ago · 3 replies
A trailing sign is a mainframe or ERP style export and no amount of multiplying will parse it. The sequence that works:
=SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),"")," ","").=IF(RIGHT(A2,1)="-",-VALUE(LEFT(A2,LEN(A2)-1)),VALUE(A2)).Honestly for 12,000 rows I would do all of it in Power Query instead. Its type conversion has a using locale option that handles separators and the trailing minus in one step, and it repeats on every refresh rather than being a one-off you have to remember next month.
Reply
Report
@kernel_ring_buf · 4mo ago
The using locale option was the whole thing. It parsed the trailing minus correctly with no formulas at all.
Reply
Report
@xlookup_lena · 4mo ago
It is buried in Change Type using Locale and almost nobody finds it. Worth remembering for anything out of a European or ERP export.
Reply
Report