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

Larry,
Here is a copy/paste copy of Brian's comments to my message.
I hope Brian does not mind.
Regards
Pertti Rönnberg


> 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







On 13.1.2014 19:15, Larry Evans wrote:
> On 01/13/14 05:10, Pertti Rönnberg wrote:
> > Thank you Paul and Brian
> > for your interesting answers and for your kindness to send them so soon.
> > I have not checked them yet but I am sure that both of them give me what
> > I was looking for.
> > I see thepros and cons in Paul's method: by splitting the calculation in
> > details you can follow both the process and the reliability of the
> > result, but at cost of space.
> > But, there is more fun in Brian's solution; I have always liked to
> > create such complex formulas partly to be familiar with the spreadsheet
> > program and it's functions but now a days mostly because it is very good
> > exercise for an old man's brain --it is a challenge and then a good
> > reason to award yourself with a drink when it finally works.
> > I think I was quite near Brian's solution, but something went wrong, so
> > I had to shout for help.
> [snip]
> Hi Pertti,
>
> I don't see Brian's reply to you(maybe he emailed you privately).
> Could you please post it so other's could see it?
>
> -regards,
> Larry


--
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