[Solved] LibreOffice - Data/Text to Columns not working

Discuss the spreadsheet application

[Solved] LibreOffice - Data/Text to Columns not working

Postby pizzipie » Tue Apr 17, 2018 3:43 am

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??
Last edited by pizzipie on Tue Apr 17, 2018 9:11 pm, edited 1 time in total.
LibreOffice Version: 5.0.5.2 - Using Ubuntu 14.04 - MySql and PHP
pizzipie
 
Posts: 32
Joined: Sat Sep 13, 2008 2:28 am

Re: LibreOffice Calc - Data/Text to Columns not working

Postby FJCC » Tue Apr 17, 2018 4:12 am

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.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6401
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: LibreOffice Calc - Data/Text to Columns not working

Postby Villeroy » Tue Apr 17, 2018 12:54 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25150
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Calc - Data/Text to Columns not working

Postby pizzipie » Tue Apr 17, 2018 9:08 pm

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
LibreOffice Version: 5.0.5.2 - Using Ubuntu 14.04 - MySql and PHP
pizzipie
 
Posts: 32
Joined: Sat Sep 13, 2008 2:28 am


Return to Calc

Who is online

Users browsing this forum: BlackRose and 17 guests