Ask Your Question
0

Text to Number conversion

asked 2019-07-18 18:04:10 +0100

Widgey Minor gravatar image

updated 2019-07-18 21:04:14 +0100

erAck gravatar image

I opened a CSV file. It came through correctly for columns but some of the numbers (both -ve and +ve) came through as text. How can I prevent this and/or convert after opening?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-07-18 18:37:49 +0100

Opaque gravatar image

updated 2019-07-18 19:01:12 +0100

Hello,

Question 1: How can I prevent this?
Your numbers in your CSV file are not recognized as numbers during import. And this is most likely due to the fact that your locale uses another default decimal delimiter that used in the CSV. For testing purpose just create a simple CSV file containing

user@system:~> cat 1.csv 
1.00;1,00

Now open the file and you'll see that one of the "numbers* is recognized as a number (right aligned) and the second as text (left aligned). Which one depends on your locale (for my german local the comma "," is the decimal delimiter and thus 1.00 is recognized as text). Obviously there are 2 solutions

  • Change your locale (either globally or during import / opening a CSV file - carefully watch the dialog which offers on category Import to select the Language, which allows to adapt to the delimiter used in the CSV file.
  • Change the CSV file to use the delimiter matching your Calc default locale delimiter

Question 2: How to convert ? The solution derives from above. So change the delimiter using Find & Replace. To do so:

  • Select the column containing the data with wrong (with respect to your locale) decimal delimiter

  • Edit -> Find & Replace and set
    Find field: incorrect delimiter (e.g. ,)
    Replace field: correct delimiter (e.g .)
    Opther options: Current selection only

  • Click OK

Note: You defintely can't convert just changing the format. Don't even try, it won't work, since formatting a cell doesn't change the data type of existing data.Formatting just changes the appearance of a data type.

Hope that helps.

edit flag offensive delete link more

Comments

See also this FAQ.

erAck gravatar imageerAck ( 2019-07-18 21:03:53 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-18 18:04:10 +0100

Seen: 51 times

Last updated: Jul 18