Apache OpenOffice Community Forum

I have been importing a CSV file of financial data weekly for a couple of years quite successfully.
Suddenly all is fine except the column containing "$1234,56".
This comes in as text now. What needs to be tweaked so that I do not have to manually convert these into values?
Thanks. in advance for any help.

In the text import dialog ensure that the Detect Special numbers box is checked.
With OpenOffice this box is unchecked by default, and will go back to the unchecked status upon the closure of the import dialog.
With LibreOffice this option is checked by default, but the setting is remembered for the next time the dialog opens.

"$1234,56" is a number with comma as decimal delimiter and a dollar sign as currency symbol. There is no way to get this special number interpreted since all "dollar locales" use the point as decimal separator.
Select the imported text column.
Apply some US or Australian number format to the selected cells and replace commas with points.

Australian number format

We use the period as a decimal separator down here; the comma would be upside down for us on the bottom of the world.

Alternatively:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2;",";MID(1/2;2;1));"$";""))
inserts the right decimal separator, removes the "$" and gets the numeric value.

robleyd wrote:

Australian number format

We use the period as a decimal separator down here; the comma would be upside down for us on the bottom of the world.

If you ever get a European file with comma decimals, choose a matching locale and you get the right numbers with points. But there is no matching locale with both comma and "$".

Example:

2.1.19;€13.897,99
3.1.19;€12.345,99

copy the two lines, paste special, choose "German(Germany)", semicolon as column separator and "special numbers".
The result will look like this with an Australian locale:

2/1/19 13897.99
3/1/19 12345.9

and like this with a US locale
1/2/19 13897.99
1/3/19 12345.9

Villeroy wrote:"$1234,56" is a number with comma as decimal delimiter and a dollar sign as currency symbol. There is no way to get this special number interpreted since all "dollar locales" use the point as decimal separator.
Select the imported text column.
Apply some US or Australian number format to the selected cells and replace commas with points.

This "." was a miss type on my part. It was a period not a comma.

That's what I was thinking when I made my original post.
Follow my earlier advice and everything should be good.
If not, by all means post back with an example of what you get along with the settings in the text import dialog

Thanks for all the help. I played with the Text import options and found a set that worked.
See the attachment. Imports as numbers again.