Using TYPE(), this ‘45.87’ is text (2) in cell G3.
VALUE(G3) returns a blank.
The cell was created by LibreOffice Calc loading a phpmyadmin Excel style .csv file and converting all the double quotes to single quotes ( " to ’ ). I need to convert all these ’ Amount ’ cells to numbers.
Thanks R
Importing from csv always look at the results immediately. A defective import is then easily made undone, and thorough check of the settings will,make the second run succeed.
Hello,
if your value shows is litteraly '45.87' (incl the quotes at the beginning and the end) use:
Edit -> Find & Replace
'
Replace All
Note(s):
If this only replaces the starting ' then you many need to check whether Tools -> AutoCorrect Options... -> Tab: Localized Options Single Quotes -> [x] Replace has been checked. If this is the case, the quote may have been changed and you may need to perform the Find & Replace a second time with copying the changed quote into the Find field.
If the operation has been performed successful, you will notice an immediate change in the value’s alignment from left (text) to right (number).
Tested using LibreOffice:
Version: 6.4.1.2 (x64), Build-ID: 4d224e95b98b138af42a64d84056446d09082932
CPU-Threads: 4; BS: Windows 10.0 Build 18363; UI-Render: Standard; VCL: win;
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE, Calc: threaded
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
If you want to keep the original data, but to get the values for calculations, you can use (e.g.):
=VALUE(SUBSTITUTE(A1;"'";"")) or =VALUE(MID(A1;2;LEN(A1)-2)) for anything like '45.87' in A1.
(This in any locale using the point as its decimal separator.)
Blockquote
Select cell(s) / column containing these values
Edit → Find & Replace
Find:’
Replace: keep empty
Other options: [x] Current selection only and click button Replace All
Blockquote
Thanks, very nice. Works well. I did not know about the “Other Options”. That’s why it didn’t work.
Please do not use Add Answer if you actually don’t answer a question but commenting an answer. Please use add a comment for that purpose. Thanks in advance …