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+unsubscr...@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
