We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Convert this '45.87' text to number

asked 2020-03-10 23:30:43 +0200

kasloman gravatar image

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

edit retag flag offensive close merge delete

Comments

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.

Lupp gravatar imageLupp ( 2020-03-11 00:57:32 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2020-03-11 00:39:22 +0200

Opaque gravatar image

updated 2020-03-11 09:22:34 +0200

Hello,

if your value shows is litteraly '45.87' (incl the quotes at the beginning and the end) use:

  • 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

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.

edit flag offensive delete link more
0

answered 2020-03-11 02:05:01 +0200

kasloman gravatar image

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.

edit flag offensive delete link more

Comments

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 …

Opaque gravatar imageOpaque ( 2020-03-11 09:23:52 +0200 )edit
0

answered 2020-03-11 00:54:31 +0200

Lupp gravatar image

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.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-10 23:30:43 +0200

Seen: 886 times

Last updated: Mar 11 '20