How do I convert "Text Numbers" created by opening a CSV Currency Column?

Currency or Numbers end up with the hidden “’” before the number. Formatting isn’t possible until these cells become numbers.

Are you selecting the checkbox for Detect special numbers on the Text import dialog box? Also, if the fields in the csv file are delimited, try unchecking Quoted field as text

Better avoid the problem by checking “Detect special numbers” and choosing the right locale for the currency. These settings are remembered and any dates/times will be import correctly as well.

Could work Menu/Data/Text to columns

Menu/Data/Text to Column , but you have to select “Text Delimiter”. Thanks.

Is anyone able to answer this question? It’s 3 years (!) and it’s still a problem in LibreOffice needing an answer!

If the format of the textual dates is consistent in the CSV file, then adjust the import filter when you import the date into a Calc document. The Calc can recognize the basic ISO and some localized date formats, and it will convert them into numeric representation.

  • The basic ISO format of the dates is “YYYY-MM-DD”. It always work,
  • But the Calc can work with many localized date formats, too.
  • If the dates are in inconsistent format, then you can try to make consistent them before you import them (by usage the find and replace function in a text editor)

Have you read the existing answers prior to adding this meaningless “answer” stating false? Answer by @mariosv is the correct one, and it’s more than three years old.

1 Like

@JamesBurke the answer from @mariosv is 100% correct, though cryptic. If you have already imported the CSV file, you need to use the “Text to columns” feature. Unfortunately the documentation for that feature currently makes no mention of converting “text numbers” to numbers (or “text dates” to dates), but that is its most common use.

If you have multiple columns that need conversion, you might need to convert one column at a time. (I’m not sure about this, but it seemed to be what I had to do to enable “Text to columns” in the Data menu.)

I managed to do this using the following procedure. Convert the cell to English/US, then find and replace with the source contents, and the values are auto-detected as numbers.

1 Like