2014 Archives by date, by thread · List index

# [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
```