Re: [libreoffice-users] a LibO/Calc problem

At 17:55 12/01/2014 +0200, Pertti Rönnberg wrote:
> Since years back I have copied my bank's digital 
> listing of my bank account(s); first using 
> MSOWord and later on MSOExcel. My intention is 
> to transform these listings so I can calculate 
> with the currency values in LibO-Calc. The 
> bank's table has four cols: colA=date, 
> colB&colC= text and colD is the currency as 
> text. Each listing consists of several hundreds 
> of events (rows). The problem is that the damn 
> bank -- against all standards -- gives the 
> currency values with a dot (".") as thousand 
> separator and "+" or "-" chars (plus or minus) 
> in the right end of each number (e.g. "987,65+", 
> "1.234,56-", "23.456,78+") -- which is against Calc's will.
> By now I have managed to get LibO/Calc to accept 
> all values less than 9.999,99:
> > first dragged (copied) the table from MSWord 
> => MSExcel; MSExcel-file saved in LibO/Calc as "ods"
I'm not sure why you want to use Microsoft Word 
or Excel.  You can open a .doc file in 
LibreOffice (as a text file) and copy and paste 
the table into a spreadsheet there.  You can open 
an .xls file directly in LibreOffice.  If your 
bank's original data is plain text or web 
material, you can import this directly into a LibreOffice spreadsheet.
> _Question:_
> What formula/function gives the same result for 
> the bank's currency values bigger than 9.999,99?
> e.g. "11.222,33+", "11.222.333,44-"
It's a bit messy, but this should work:
=VALUE(RIGHT(Xn;1)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Xn;".";"");"+";"");"-";""))

> Cannot get the Fixed() -- when trying to 
> eliminate the separator/dot that way -- working in this 'project' either.
This is no help.  If you already had correctly 
interpreted numbers (which you don't: that's the 
problem), FIXED() would convert these to text 
(with which you then couldn't calculate) - 
exactly the opposite of what you are trying to 
do.  Once you have your real numbers, you can 
control their appearance by choosing appropriate cell formats, of course.
I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted