How to remove single quote in front of numbers in a cell

English
,
#27

Sorry for my bad “style” but I’ve been at this for a long time and I use this program every day.

corrupted user profiles

Interestingly, I deleted my user profile only a week ago as part of a suggestion to remedy a 100% crash I had with vertical scrolling (and or page up page down). So in my case, it’s a fresh profile. By the way, doing so didn’t fix the crash, but turning off hardware acceleration completely fixed it, so if anybody is getting scrolling crashes, that solution worked perfectly for me, as did the copy-to-notepad workaround for this current issue. The giant problem with Calc (which I’m still using instead of Excel) is not so much a bug as a design error such that the eye perceives a single border between two cells but the UI for changing the appearance of that border thinks that it’s both the left side of B2 and the right side of A2, and behaves in accordingly unpredictable and bizarre ways. It’s the epitome of WhatYouSeeIsNOTWhatYouGet. Excel is fine for the math; terrible for the appearance of the data. Calc seems to have found everything bad about Excel and fixed it, but then they found the few graphic elements that do work in Excel and broke them unnecessarily!

#28

Hallo
@beatlefan a “number” starting with a decimal dot isnt a number, so you need to add a 0 in front:

search for 
^\.\d+$
replace with
0&
[x]regular Expression
#29

Excel works 100% same way in this regard, as Calc does.

This argument appears again and again. Every newbie comes across this (myself included). E.g., the reasons are explained in LibreOffice Calc not computing functions (showing function, not output) - #18 by mikekaganski. Then, we have tdf#150264 filed recently, with some discussion and mockup.

And I simply do not believe that the steps outlined in this exact thread (both those posted by myself, and those listed in the FAQ that @erAck created) didn’t work when followed correctly. I would be glad to help a question like “I follow these steps, it doesn’t work, let me explain what I see on each step and where it diverges” … it would be useful also to find ways to improve our description if they happen to confuse people (even though I create screencasts to reduce that possibility) - maybe there’s some set of data that resists our steps…

1 Like
#30

Thanks! So “.16” is just text as far as Calc is concerned but “0.16” is a number?

3 questions:

  1. How did I inadvertently create the phantom apostrophes in the first place? Or to ask it a different way, how could I create a cell with a phantom apostrophe so I can try your test? (I used the notepad workaround so I no longer have any cells with phantom apostrophes).

  2. What does the phantom apostrophe actually mean technically speaking?

  3. If I’d selected the whole column and formatted it as text would that have removed the phantom apostrophes?

#31

No. You likely respond to this:

which is wrong. And the very presence of the apostrophe - the visual clue, that tells user “the value contained in the number-formatted cell looks like a number, and if such data was entered anew into the cell, it would be recognized as a number; but it is in fact a string, so we show it to user with this apostrophe” - proves that.

Yes. Then there would not need to be a visual clue that the data is text - the cell is textual anyway.

1 Like
#2

The apostrophe is not part of the text contained in the cell but only an indicator for the situation that the content is treated as text despite the fact that it could be recognised as numerical.
If allowing RegEx you ‘Search For’ .* (anything) and ‘Replace With’ & (everything found) a new recognition process will be applied with the effect that the content is reciognised as a number and displayed in the chosen (or automatically assigned) format.
If the contents you want recognised are filling a range of a single column you can achieve the result also applying the tool ‘Data’ > ‘Text to Columns…’ (without additional measures).

(There are a lot of threads about the "apostrophe issue"in Calc. Just look for them.)

7 Likes
#3

Also be careful not to use Data▸Text to Columns... if some cells in the range contain formulas (they would be deleted, only the values are retained).

Regards

1 Like
#4

+1 to @PYS; still, in case of formula, there would not be any apostrophe in the respecting cells’ edit boxes. In my opinion, the Text to Columns is the ultimately correct tool for data conversion (while in case of Find&Replace, the conversion is kind of side effect).

#5

Data > Text to Columns worked fine

#9

Paste complete column in notepad or gedit and the copy back.

1 Like
#10

In this case, you don’t need an intermediate application; simply paste back as unformatted text (Ctrl+Alt+Shift+V).

#11

Yes Possible.

#12

As described by @Lupp, the apostrophe is an indicator that a cell is formatted as numeric/date value, but the contents was textual at the moment of setting the format (which could be converted to a number). In this situation, Calc keeps the contents as text (because formatting of an existing value must not do data conversion), and indicates that by showing the apostrophe in edit box.

The proper tool co convert data in Calc is Data→Text to Columns. One selects (a part of) a column, and starts the tool:

image description
image description630×652 24.7 KB

The interface of the tool looks alike CSV import. In the tool, you select the proper type for each column; e.g.,for integers, Standard fits well; if your numbers contain decimal dots (not commas), the proper type is US English; for times, the proper type could be one of dates. Here you may also split texts to several columns using proper separators, and set each resulting column the proper type; or even convert numbers back to text.

The tool is explicitly designed to convert data (as opposed to format data), and I recommend using it wherever one needs such conversion, including “removing the leading apostrophe”, as in this case.

1 Like
#13

Thank you very much, this is helpful - I did not know such tool existed - it works very well. First set column type and then use the tool on the relevant column. The tool name is a bit cryptic, but that’s fine.

1 Like
#14

Just adding the standard FAQ for this.

1 Like
#15

Not every FAQ subject is settled on this level. In this case the text is very clear and helpful. Having known it 1 1/2 years ago, I surely had simply linked it in.

#16

I only created it half a year ago, so nothing you missed :wink:

#6

Try this - first, get rid of the $ with Find and Replace. Then:

  1. Select the column in which the digits are found in text format. Set the cell format in that column as “Number”.
  2. Choose Edit - Find & Replace
  3. In the Search for box, enter ^[0-9]
  4. In the Replace with box, enter &
  5. Check Regular expressions
  6. Check Current selection only
  7. Click Replace All
1 Like
#7

This worked for me. It removed the leading ’

#8

Working thanks.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.