|
|
Post by ladywhitenap on Aug 5, 2018 9:48:05 GMT
ablrateWhen I down load the .csv versions of my investment or forthcoming repayments, the monetary values are of the form "£914.26" and so I cannot manipulate these as values eg to add them up. Is there a simple way to convert these to ordinary numbers please either in the spreadsheet or even change the format is which they are sent by Ablrate PS I'm not that much of an expert excel user but can do basic things and all my software is old versions eg excel 2002 sp3 TIA LW
|
|
|
|
Post by spareapennyor2 on Aug 5, 2018 12:21:13 GMT
ladywhitenaptry libreoffice possibly need to check if your text is Unicode (UTF-8) google your friend
|
|
ianj
Member of DD Central
Posts: 654 
Likes: 519
|
Post by ianj on Aug 5, 2018 12:33:40 GMT
I have to admit to using even older software, MS Works (why pay for something else when this costs me nought), and, if working on a downloaded .csv, I would use the EASY CALC functions =VALUE(REPLACE(cell no,0,2,"") which results in the numeric format of whatever follows "£". I would assume there will be equivalent functions in Excel.
My preferred way around this problem is to 'copy & paste' from the screen into a spreadsheet, but this (for me) requires a second screen to be displayed in order to see a month's worth of repayments.
Of course, in an ideal world, one would be able to specify a variable date range for repayments and the resulting display would show interest, capital repayments (if amortising) and total repayments, all on a single screen, which could be downloaded...with appropriate numeric values, naturally. Not much to ask, and probably requiring considerably less effort to achieve than was expended on the recent 'bidding' problems.
|
|
|
|
Post by ladywhitenap on Aug 5, 2018 13:11:14 GMT
ianj Thanks Ianj nice to find another Luddite. I will poke around in my excel for similar functionality. spareapennyor2 My Excel data import wizard does not seem to offer UNIcode but Libre-office Calc does so thanks for that suggestion. Works perfectly. I've been gradually realising that I might have to migrate to Libre-office but I have so many old spreadsheets in Excel and dreading coming across compatibility issues. I already find that is not totally compatible with newer Microsoft word docx files that I sometimes get sent. Thanks Both LW
|
|
empirica
Member of DD Central
Posts: 326
Likes: 235
|
Post by empirica on Aug 5, 2018 16:53:53 GMT
Slightly more 'techie' answer.
Assuming the dodgy import is in cell A1, copy =RIGHT(A1,LEN(A1)-2)*1 into a neighbouring cell. The formula effectively strips the £ out leaving just the 914.26 which the '*1' part at the end ensures is returned as a number.
Should work whether its £914.26 or £10,914.26 or £4.26 etc.
|
|
|
|
Post by ladywhitenap on Aug 5, 2018 18:59:28 GMT
Slightly more 'techie' answer.
Assuming the dodgy import is in cell A1, copy =RIGHT(A1,LEN(A1)-2)*1 into a neighbouring cell. The formula effectively strips the £ out leaving just the 914.26 which the '*1' part at the end ensures is returned as a number.
Should work whether its £914.26 or £10,914.26 or £4.26 etc.
Yes that should work as does =VALUE(REPLACE(A1,1,2,"") Where A1 is the dodgy cell. REPLACE strips off the first two characters and VALUE returns a number. LW
|
|
withnell
Member of DD Central
Posts: 550
Likes: 491
|
Post by withnell on Aug 5, 2018 19:08:38 GMT
Potentially simpler would be to do a Find/Replace, Find "£" and replace with blank!
|
|
empirica
Member of DD Central
Posts: 326
Likes: 235
|
Post by empirica on Aug 5, 2018 19:56:44 GMT
Potentially simpler would be to do a Find/Replace, Find "£" and replace with blank! I tried that (kind of)* but then couldn't 'make' the remainder a usable number _ even the usual '*1' trick failed, but your mileage may vary and I by no means profess to be an Excel expert. *I used =substitute() rather than F/R
|
|
nick
Member of DD Central
Posts: 1,056
Likes: 825
|
Post by nick on Aug 7, 2018 15:05:38 GMT
Slightly more 'techie' answer.
Assuming the dodgy import is in cell A1, copy =RIGHT(A1,LEN(A1)-2)*1 into a neighbouring cell. The formula effectively strips the £ out leaving just the 914.26 which the '*1' part at the end ensures is returned as a number.
Should work whether its £914.26 or £10,914.26 or £4.26 etc.
Yes that should work as does =VALUE(REPLACE(A1,1,2,"") Where A1 is the dodgy cell. REPLACE strips off the first two characters and VALUE returns a number. LW Another quick and easy way I often use when faced with extra characters in value fields is to do a simple find and replace, eg highlight all affected cells, ctrl F, and insert "£" in the find field, leave the replace field blank, and press replace all. This will delete all references to £ leaving clean numbers. If there is a space between the characters and the figures, this needs to be included in the find field. I use this method a lot and find it generally quicker than pasting and manipulating formulae unless there are a lot of other computations that the figures feed into in which case a template would be better.
|
|
