' symbol appears during paste from gnumeric to librecalc

[umap]

when copying 6 columns from gnumeric to librecalc one number column inserts the ’ symbol before the number.

Attempted solution 1 :

1. copy ’ symbol
2. ctrl h
   3.paste ’ into Find: box
   4.select Replace All

Result: Search key not found

Attempt solution 2:
Format cell displays :
Category: Number
Format: General
Decimal places: 5

what is the correct procedure to prevent erroneous characters during paste ?
Is the ’ symbol a non-searchable character ?

The existence of the ’ symbol is corrupting formula cells that expect a number with no characters.

[FelipeAle]

After open the dialog Find And Replace (Ctrl+H), in the first box (Find), paste de character ^, then the symbol you referring it.

Press the button Find all, then Replace All.

Make sure to mark the checkbox Regular Expressions before the procedure.

[umap]

I tried with Regular expressions checked and again unchecked, the ^ before ’ symbol is not making ctrl h find or replace - getting same error

[EarnestAl]

Probably best to open the gnumeric sheet in Calc if it is saved in a common format

For pasted data, this article should help How to convert number text to numeric data

[joshua4]

Just a thought: you could do a clean-up sweep via macro.

Here is a macro that takes any cell with a string value that LO BASIC can interpret as a number and converts it to numbers. It operates on the used range of the active sheet. I just wondered if it was as easy as assigning Cell.Value = Cell.String. Essentially, yes, it was. Try out on a copy! This doesn’t have much testing behind it.

Option Explicit

Sub ConvertTextToValue()
	Dim Sheet As Object
	Dim Range As Object
	Dim Cell As Object
	Dim Row As Integer
	Dim Column As Integer
	
	Sheet = ThisComponent.CurrentController.ActiveSheet
	Range = UsedRange(Sheet)

	For Row = Range.StartRow To Range.EndRow
		For Column = Range.StartColumn To Range.EndColumn
			Cell = Sheet.getCellByPosition(Column,Row)
			If Cell.String <> "" Then
			On Error Resume Next
				Cell.Value = CDbl(Cell.String)
			On Error Goto 0
			EndIf
		Next Column
	Next Row
	
End Sub

Function UsedRange(Sheet As Object) As Object
	Rem From erAck/AskLO
	Dim Cursor As Object

	Cursor = Sheet.createCursor()
	Cursor.gotoStartOfUsedArea(False)
	Cursor.gotoEndOfUsedArea(True)
	UsedRange = Cursor.RangeAddress
	
End Function

[umap]

Thank you everyone for contributing so many solutions. @ FelipeAle solution auxiliary column, multiply the range by 1 was simplest to execute in this spreadsheet. @ erAck has a point mikekaganski explanation and referenced faq demonstrate a few different ways to fix 'symbol decoration corrupting calculations.

[mikekaganski]

The ' symbol in the formula bar (which doesn’t show in the cell!) shows you that the cell is formatted as number, but contains a string that *could be potentially converted to a number.

A TL;DR background: any cell may contain values of a few types: a number, a text, an error. A number may be represented in many ways (as number having different precision, as date, as boolean, as fraction, etc.); but text is just a string that is not considered to have a numeric value. The decision what is contained in the cell is done at the moment of entry, when some complex rules help Calc to interpret the characters that user entered, and either convert them to a number, or to a formula, or keep them as plain text. Pasting content from clipboard may also assign types to the pasted values - depending on the data in clipboard (and that also depends on the source application, what it defines there in the clipboard).

So when you pasted, the numeric cells were populated with texts. This is shown to you as the ' symbol, and helps you understand why, e.g., formula =SUM(your_cells) will give you 0 - just because the cells don’t contain numbers, but texts looking as numbers. The symbols is just a decoration, not part of the string itself - and thus, not searchable.

There is FAQ on converting the texts to numbers. You may also consider selecting a different clipboard format when pasting.

[FelipeAle]

Got it! Try to use just ^$ in the Find box.

Remember to format the selected range as number until the procedure.

–EDIT–
Sorry, I tested once, and it worked. I tested again and nothing happend.

So try another solution.

In auxiliary column, multiply the range by 1.

[erAck]

Multiplying a text by 1 also may or may not work. It depends on the actual text and on the detailed calculation settings how to calculate with text. Better is replacing the underlying data with the correct numeric values. Which is lined out in the FAQ mikekaganski linked to.

[Villeroy]

You can avoid the problem if you open both documents with the same spreadsheet application.

LO has 2 reliable conversion methods for numeric text:

1. menu:Data>Text to columns… uncheck all column separators, always check “Detect special numbers” in order to include dates, times, currencies and alike.

2. menu:Find&Replace…
   [X] Regular expressions
   Search: .+ [a dot and a plus]
   Replace: &
   [Replace all]