Changing Text to numbers

[Lazlo]

I am new to Libre Office and am trying to import a csv file into a spreadsheet. Once imported, the dollar figures have a ’ in front which seems to mean that they are text not numbers.
I have tried to change the formating but it does not seem to work.

The help section does not seem to help as it only shows how to change numbers to text, not vice versa.

Can anyone help as I really need to get these columns into number format.

Thanks for any help.

i have a large table with lots of zeros. a lot of formulas did not work on the zeros

for example:
one row consists of 52 zeros and one time number 710. as average is gives 710, as a mean it shows 710 and as minimum it gives 710.

it looks like the software does not recognize the zeros as numbers.
i checked if the numbers where preceded by a ’ and could be interpreted as text. this is not the case
however; when i change some zeros into 1’s the average, mean and min do not change. they stay 710
i have checked the formulas that calculate the average and median and the range is correct.
i have selected the whole table and formatted the cells within the table as numbers
i rechecked the cells and now it shows that before all zeros there is a " ’ " .
i used the find and replace function to search for every " '0 " and replace it with a zero. the problem is that it does not find any cells containing " '0 " while i see them in my screen.
dont know what to do anymore…

am i missing something?
or is it a bug?

please help

=== Start Editing
Would the one who edited the very old question recently, please, clearly mark what he (f/m) changed. Otherwise a modification/enhancements of the answers are uselessly aggravated. (@Lupp }End Editing===

[Lupp]

Infornation needed.
Did you already try the ‘View’/‘Value Highlighting’ tool?
Who made the sheet, what file format was it, and what do you know about the settings?
Did you look into the ‘Format Cells…’/Numbers’ tab for some of the strange zeros? What did it show as format code?
Would you mind to tell your version of LibO?
The number of used cells in a specific row should not be relevant.

[Lupp]

Cells showing the apostrophe indicating a content acceptable as a number but entered expressly as text, do not contain that apostrophe actually.It is an indicator only temporarily generated for the formula bar of a selected cell or during editing of its content.

[BOB2PLAY]

I have tried several approaches and NOTHING gets rid of the ’ in front on the $ in every cell that was designated to be a CURRENCY-- now everything is '$123.45 type and NO formulas work- AARRRGGHHH… program has more bugs than ORKIN

[Lupp]

Sorry. Such a comment isn’t a reasonable way to get help concerning an issue.
It’s obvious, e.g, that an arbitrary currency value given as a string in one or another currency format, must not be recognized by a LibO set to a default document language having a different default currency or currency formats. Similar issue if a document language or a cells Number>Langauage property is set independent of the respective default.
Using any software successfully requires active thinking now and then. Expressing frustration may be “human” as erring is, but is useless, and may frustrate those who try to help.
What I described in my answer years ago still works for me in V 6.2.2.2 under the mentioned restrictions.

[loudal]

I took to trouble and registrated. Just to show you, how a frustrated human reacts. Why THE FUCK if I copy a number from a table of numbers does it show in a spreadsheet, that supposedly works with numbers, as a text? How THE FUCK is that not possible to revert with just one click. And after that blabber about years ago, why THE FUCK has nothing been done if you see the 64k views? Do you get money for people looking up issues with your software. I assume you are millionaires by now and just dont give A FUCK. Thank you, that helped more than your documentation foundation.

[mariosv]

I think the easy way is select the range in a column or the column (only one column of data) and use Menu/Data/Text to column.

But first in the import you can select for every column the format for import, select the column and in the column type box select what you need.

[Lazlo]

Thanks for the help. Worked like a charm.

[howil]

Thanks “Text to column” worked fine for my cell-content ('7:45) when using ’ in the field “Text delimiter”.

[Lupp]

Setting cells formatted to “do not recognise numbers and formulae” aka ‘Text’, ‘Numbers’ format code “@” back to a true ‘Numbers’ format will not automatically change the type of the content (where applicable). A string coding a number or a formula has to run through a kind of editing process to now be recognised as a number or a formula respectively.
To trigger a new recognition after switching off the “@” mode for all the cells you want to recognise numbers and formulae, you best use the ‘F & R’ tool:
Call ‘Edit’ > ‘Find and Replace…’ (Ctrl+H by default)
View ‘Other options’
Set chosen ‘Regular expressions’ , ‘Search in:’ Formulae
Put .* into ‘Search For:’ and & into ‘Replace With:’
Call ‘Replace All’

Done!
Of course you may restrict the procedure to ‘Current selection only’, but this should not be necessary as any cell content is just newly input itself. The only difference is the fresh recognition which will neither affect cells with already recognised contents nor cells still left in the “@” mode.
For completeness: For single columns you may use the > ‘Data’ > ‘Text to Columns…’ tool with advantages. Be careful with the separator settings and click on the column label of the preview window (‘Standard’) in advance of running the tool.

Possible problems (as always) locale dependent recognition, mainly calendaric dates.
Giving the details would require to write a special guide.

[corjan]

that worked! really happy with that!!

[Lupp]

Clicking the checkmark next to the start of an answer you can mark it as correct/acceptes. This may help other users searching for help to select relevant hits.

[corjan]

thanx, have done that.

[chimak111]

Another help is to tick the box next to “Detect special numbers (such as dates)” which is seen in the Import Options pop-up window.

[frostyjhammer]

Fantastic, thank you! This solves the problem before it can occur, by assuring CSV values with leading dollar signs are imported into the sheet as numbers – not strings.

[qed]

The CT2N plugin might be what you want:

http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

[BAUSMUS]

Highlight the cells you want to remove the ’ in front of and do a find and replace.
find: .*
replace: &

If the output is still text, use the Value() function to convert it to a number.