I am running Ubuntu 16.04 and using LibreOffice 5.4.3.2.
I downloaded my purchase details from one of the 'Box Stores'. The download was a .csv file. The format of the 'price' field is like $12.95.
These 'numbers' are, in fact, not numbers but text. Since there is no way, that I know of, to convert text to numbers in LibreOffice Calc I have used the Data/Text to Columns menu item successfully in the past to get real numbers.
For some reason this is not working now. I have been highlighting the column I wish to process including the field name. I have then gone to Data/Text to Columns and then picked OK without changing anything. Nothing happens!
Am I doing something wrong here??
[Solved] LibreOffice - Data/Text to Columns not working
[Solved] LibreOffice - Data/Text to Columns not working
Last edited by pizzipie on Tue Apr 17, 2018 9:11 pm, edited 1 time in total.
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Re: LibreOffice Calc - Data/Text to Columns not working
Have you set the format of the column to something that will accept the leading $, some kind of currency format?
Also, you should be able to import the currency values as numbers so that you don't have to use the Text to Columns trick. When I open a csv I get an import dialog. If I check Detect Special Numbers, currency and dates come in as numbers.
Also, you should be able to import the currency values as numbers so that you don't have to use the Text to Columns trick. When I open a csv I get an import dialog. If I check Detect Special Numbers, currency and dates come in as numbers.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: LibreOffice Calc - Data/Text to Columns not working
Don't fix wrongly imported data. Import them correctly.
The solution is always (hundreds of cases discussed here):
1) Choose the correct import language
2) Check option "Detect special numbers"
Don't ignore options you don't understand. There is a help button on the import dialog.
1) In your case the import language may be English(USA) because of the $ Currency. If your data set includes any US dates (12/31/1999), English(USA) is the right language for sure. If you have to import normal dates (31/12/1999), English(Australian) would be a better choice (Australians use $ currency and normal dates).
2) Always check this option. Only in very rare cases you want to turn it off.
The solution is always (hundreds of cases discussed here):
1) Choose the correct import language
2) Check option "Detect special numbers"
Don't ignore options you don't understand. There is a help button on the import dialog.
1) In your case the import language may be English(USA) because of the $ Currency. If your data set includes any US dates (12/31/1999), English(USA) is the right language for sure. If you have to import normal dates (31/12/1999), English(Australian) would be a better choice (Australians use $ currency and normal dates).
2) Always check this option. Only in very rare cases you want to turn it off.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: LibreOffice Calc - Data/Text to Columns not working
Thank you both for a quick reply.
I have no control over the import format. The store just says export which you click and it does it's thing. here are no options.
The step I missed was formatting the column to currency. When I did that all is well.
Thanks,
R
I have no control over the import format. The store just says export which you click and it does it's thing. here are no options.
The step I missed was formatting the column to currency. When I did that all is well.
Thanks,
R
Libre Office Version: 6.0.7.3
Build ID: 1:6.0.7-0ubuntu0.18.04.10
Build ID: 1:6.0.7-0ubuntu0.18.04.10