Downloading statements included formatting '($22.22) that I can’t seem to remove AND using the SUM function doesn’t work on it either . HOW do I remove this formatting where FUNCTIONS will work and generate correct totals?
Using the find/replace functions seems to remove the formatting BUT the function =SUM always return a zero. What is the trick?
How does '($22,22) get into your spreadsheet? You don’t type it in like that, do you?
Heck NO! I’m downloading bank statement in csv formatting to use for taxes and it opens like that. I can’t get rid of it, or it appears to disappear with the find/replace option but functions DO NOT WORK on it. I’ve resorted to re-entering all of the numbers (by hand, painstaking) and the SUM function will work but only using the function wizard. Enter =SUM(cell1;cell50) will NOT work for some odd reason. What am I doing wrong here?
So you import a text file where the text ($22.22) represents the decimal number -22.22.
Click “Detect special numbers” in the import dialog and numbers will be numbers, dates will be dates.
Ok, I’ll try it now.
IT WORKS!! Thanks much!!! Now for my last question: I’m used to using the Excel function =SUM(A1:A15) to obtain a total from A1 thru A15 BUT L/O Calc doesn’t seem to work that way. Entering this formula into L/O Calc will give the total of A1 + A15 only. What am I doing wrong??
Exactly as with Excel, SUM ignores text values.
menu:View>Highlight Values [Ctrl+F8] highlights numbers in blue.
By default all numbers are aligned at the right cell border whereas text is left aligned. Jjust do not enforce any alignment and you see the type of cell value instantly.
=ISNUMBER(A1) checks if A1 is a number.
We can’t know how text values get into your sheet. Most people do not check “Detect special numbers”.
Open your text file with a text editor and post one or two lines of plain text. Replace confidential strings with X or something.
All of the numbers I’m working with are in Blue and aligned right. Here’s a few samples. Would the “$”
prevent the sum function from working? In the following: =sum(a1:a10) is the colon the correct delimiter?
$13.12
$30.78
$33.03
THANKS VERY MUCH for your help! You’ve saved me a lot of headaches and time. 
What was the problem?
I can only guess, but maybe you had a typo and wrote
=SUM(A1;A15)
and depending on your locale also a comma , instead of colon : will give the same (undesired) result, because A1;A15 is no range, but A1:A15 is.
The first problem was not using the “Detect special numbers” option. Everything was importing as text.
Regarding the cell delimiter: In reading some of the online help, it indicated to use a comma, or semicolon. I tried those with no luck. Since I was used to using the colon (
I tried it and still no luck. I can only assume that importing everything as text was the MAIN ISSUE. Since I started using the “Detect special numbers” option, verifying what I’m working as numbers by using the “value highlighting” option in the View menu, everything seems to be working now even using the colon to specify a range of cells to obtain the totals. I THANK YOU again for helping out.
There is one last question: My bank (annoyingly) will only allow you to export 90 days at a time so downloading the info for 2021 I had to download 5 files. Do you know of an option in Calc that you could select these 5 files and import them into ONE SPREADSHEET? Currently I’m opening each one, copy/paste them into ONE SPREADSHEET, then close the others once they’ve been copied. Time consuming! There must be an easier way to do this??
GREAT! Thanks again for your help, you’re a wealth of info…