Percentage with bracket as negative

users
#1

Hi,
I want to use feature Insert - Sheet from file and i have file.txt that
contains like this:

    1|03/10/2015|14-1403837|SARIPAH | 24 |Rp

130,000,000 |Rp 3,799,530|Rp 0|Rp 1,600,000|
0.0%|Rp 36,296,000|Rp 6,929,000|Rp 130,000,000|Rp 0| 0
     0| (24.86 % )|Arr| JAMES BOND |Y |

    1|02/10/2015|17-2202200|MAKMUR | 24 |Rp

25,060,000 |Rp 828,800|Rp 0|Rp 1,600,000|
0.0%|Rp 6,452,000|Rp 1,313,000|Rp 25,060,000|Rp 0| 0
     0| (25.24 % )|Adv| ALEXANDER |Y |

How to make LibreOffice can detect these (24.86 % ) and (25.24 % ) as
negative percentage value?

I already try many way, but still can not make it read as negative
percentage value.

Please help.. thanks!

Best Regards,
Yudi Wijaya

#2

Always turn on "special numbers" option. This will also import correct
dates.

#3

This is not enough. These data are too bad.
menu:Find&Replace...
Additional Options
[X] Regular Expressions
Search: ^\s*\(([[:digit:].]+)\s*%\s*\)\s*$
Replace: $1%
[Replace All]

And replace all Rp with nothing in order to get the numbers.

#4

Dear Andreas,
First of all.. thank you for your reply :smiley:

Your 1st method doesn't work (turn on special number), you're right.. the
datas are too bad.

Your 2nd method almost works.. but i think need some modification
Search: ^\s*\(([[:digit:].]+)\s*%\s*\)\s*$
Replace: $1%

I tested it, if the data is (25.24 % ) will replace it with 25.24%
The problem is LibreOffice only recoqnize 25,24% instead 25.24% (so must
use "," than ".")
For example if i change the data into (25 % ), then your regular expression
correct.

Do you know how to replace . (dot) with , (comma) ?

I already tried use English (UK) but can not

Thanks!

Best Regards,
Yudi

Best Regards,
Yudi Wijaya

#5

o Insert your sheet as it is.
o In a spare column, enter:
=VALUE(SUBSTITUTE(SUBSTITUTE(Xn;"%";"");".";","))/100
and copy it down the column.
o Copy the resulting values and paste them back over the source values, but using Paste Special ... (or Ctrl+Shift+V) instead of ordinary Paste and ensuring that Numbers is ticked but Formulae is not.
o Format the resulting column as Percent.

The formula removes the % signs and replaces dot with comma, leaving "(24,86 )" in the case of your first example. The VALUE() function converts this text to a number, interpreting the parentheses as indicating negative values. Dividing by 100 creates the real value required - to be properly displayed when formatted as Percent.

I trust this helps.

Brian Barker

#6

The currency comes with comma as thousands separator. This would be in
conflict with any comma as decimal separator.
I would not handle this mess with spreadsheets. First I would blame the
creator of this ugly shit. Ask for clean text data with no leading and
trailing spaces, currency symbols "Rp" in extra columns and proper
numerals. If this is not an option, I would write a simple script to
clean up the mess.

#7

OK, this is what I can achieve with a simple text editor:
Replace all "Rp" with "Rp|"
Replace all " % " with "%"
Replace all %) with )%
Copy all and paste into a spreadsheet with English(UK) as import locale.
Both, space and | as column delimiters with "Merge" option and "special
numbers" option.
This imports all dates, percents and currencies as correct numeric values.

#8

Dear Andreas,
First of all.. thank you for your reply :smiley:

Your 1st method doesn't work (turn on special number), you're right.. the
datas are too bad.

Your 2nd method almost works.. but i think need some modification
Search: ^\s*\(([[:digit:].]+)\s*%\s*\)\s*$
Replace: $1%

I tested it, if the data is (25.24 % ) will replace it with 25.24%
The problem is LibreOffice only recoqnize 25,24% instead 25.24% (so must
use "," than ".")
For example if i change the data into (25 % ), then your regular expression
correct.

Do you know how to replace . (dot) with , (comma) ?

I already tried use English (UK) but can not

Thanks!

Best Regards,

The currency comes with comma as thousands separator. This would be in
conflict with any comma as decimal separator.
I would not handle this mess with spreadsheets. First I would blame the
creator of this ugly shit. Ask for clean text data with no leading and
trailing spaces, currency symbols "Rp" in extra columns and proper
numerals. If this is not an option, I would write a simple script to
clean up the mess.

Coming in late here, but this looks like reports I import from our accounting system up to 4 times an hour.

    1|02/10/2015|17-2202200|MAKMUR | 24 |
    1|03/10/2015|14-1403837|SARIPAH | 24 |

I think Andreas regexp is good and could be used with a little tweek. Part of your problem may be to do with locale.
The report uses point as decimal and comma as thousands separator. I think you are saying your locale uses comma as decimal and point as thousands separator.
Do you have to import these often.
Steve

#9

That is simple. Also minimal processing with a script, in fact a few simple lines with sed.
Steve

#10

Yes, and there are negative percents in braces with lots of spaces and
the % within the braces.
Calc has no problem with (20)% which correctly imports as -0.2. Calc it
does not interprete (20%).
I could not find a locale for currencies with leading "Rp". Indonesian
should work because it has such currency formats but Indonesian import
locale gives text.

And yes, a trivial sed script can fix this. But I'm not familiar with
sed and most users use Windows.
Since LO comes with Python, Python could provide a cross-platform solution.