[libreoffice-users] a LibO/Calc problem

Happy New Year Dear LibO experts,

Since years back I have copied my bank's digital listing of my bank 
account(s); first using MSOWord and later on MSOExcel.
My intension 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"
> in Calc => function TRIM(D5) to get rid of non-printable chars

> formula (in E5) :
removes the separator/dot and the "+" sign and changes the value from 
text to number
/=VALUE(IF(RIGHT($E4;1)="+";IF(MID($E4;2;1)=".";CONCATENATE(MID($E4;1;1);MID($E4;3;LEN($E4)-3));MID($E4;1;LEN($E4)-1));0))/
> formula (in F5):
removes the separator/dot and the "-" sign and changes the value from 
text to number
/=VALUE(IF(RIGHT(//E4//;1)="-";IF(MID(//E4//;2;1)=".";CONCATENATE(MID(//E4//;1;1);MID(//E4//;3;LEN(//E4//)-3));MID(//E4//;1;LEN(//E4//)-1));0))/

> copy(dragged) down the 100-300 rows
> Copy(cols E&F) => PasteSpecial to colG:H gives the desired list of 
incomes and expences separated in their own cols as acceptable numbers

_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-"
I have tried to define the "." using the Find() - but have had trouble 
with it when nested in another function/formula (e.g. an IF() function); 
the definition of it in Calc/Help is obviously at least unclear if not 
wrong.
Cannot get the Fixed() -- when trying to eliminate the separator/dot 
that way -- working in this 'project' either.

Any advice and help is greatly appreciated.
Best regards
Pertti Rönnberg
computer: PC, win7prof/64bit; LO4.0.4.2


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