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

Brian,
Your formula workes perfect! Fantastic!
And I learned a little more about creating formulas.
Thanks again!
Cheers ; skål (swedish) ; kippis (finnish)
Pertti Rönnberg


On 13.1.2014 3:13, Brian Barker wrote:
> 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