hi all,
i cant sum up cells in a column and ive checked if all the cells i want to add up contain special characters but they dont, there all
£3.84
£10.65
£1,463.65
etc etc
ive done in an empty cell this
=sum(a1:a40)
but i get a result of 0
any help please
thanks,
rob
most likely text cells, like here : Simple sum isnt working on range of cells - #7 by PKG
make sure you get £ from the cell format, not in cell value.
ok i see, all my cells i want to sum up contain £ how do i get rid of all of them please
thanks,
rob
Ctrl+H or Edit > Find and replace
replace all with nothing, it should convert your cells to numbers.
ok im getting there
so ive done a find/replace (had to tick regular expression) for “£” and that got rid of all the pound signs
then i highlighted all the columns and format cell “currency” but thats added to every cell a ’
ive tried to get rid of the ’ using the find/replace but that didnt work
but interesting when i do a sum now (in a cell to sum up lets say a1:a40) and then i delete manually all the ’ infront of the cells it starts adding up, so its working
any help
Please, please, please. Always, upioad a sample file! From the beginning, all your cells were formatted as text (why?). You removed the “£” from the text, and the numbers remained text due to the inadequate number format. Cell values (text or number) never change by mere change of formatting.
Select those cells, call the replacement again and replace .+ with & with options “current selection” and “regular expression” being checked.
There is an faq-entry for the different methods (I like text in columns).
https://wiki.documentfoundation.org/Faq/Calc/How_to_convert_number_text_to_numeric_data
Where did you get the original amounts from?
Did somebody actually enter the pound sign in every cell?
Was content imported from a csv-styled file?
If you have the choice:
Never enter amounts as text!
Consider what I demonstrated in the attached example.
ask106064AmountsAndUnits_hereCurrency.ods (18.7 KB)
i got the xls from santander so it came like that
but its no biggie now i know the issue, i just have to manually delete all the single quotes from the values now ive deleted all the £ signs from them and made them into currency cells
Yes. It’s ridiculous what banks nowadays provide as “data”.
I’m also a victim (HVB/Unicredit in my case).
is there an easy way to delete all single quotes ie ’ as when i do a find/replace and using regular expression it says “search key not found”
Have you ever thought of sharing a sample file? 
Are you kidding the Ask site?
The apostrophes weren’t mentioned in your original post.
A reliable answer would require that you upload a reduced version of your sheets file (.xls in this special case). Anything possibly confidential should be removed, of course.
If the apostrophe only is shown as an attribute saying “Text intended though content would be recognized as number by defaut” it isn’t actually contained in the cell as a character, and can’t therefore be found by any SearchAndReplace process.
What your test fil shows is in obvious contradiction to what you posted so far till now.
thats because i copied/pasted a snippet of my xls only 3 values but as you can see from it cell a1:a3 they have before the value a special character ie ’
i try to find/replace with regular expression but it cant find any ’
This is turning into a pure waste of time. If I ask for a “reduced version” I don’t mean a copy looking completely different from what you described originally.
In the attached file, cells A1:A3 contain the text (not a number) “20.00”.
The SUM function does not take into account cells that contain text when summing a range of cells.
