Ask Your Question

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

asked 2017-01-26 00:07:15 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have cut and pasted numbers with dollar sign [ $ ] looking like this [ $29,825 ] that are formatted as text. When I format them as "currency" a single quote character [ ' ] appears in front of them. How to remove the single quote character? "Find and Replace" command doesn't find the character.

edit retag flag offensive close merge delete

5 Answers

Sort by » oldest newest most voted

answered 2017-01-26 00:30:58 +0100

Lupp gravatar image

updated 2017-01-26 00:32:45 +0100

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

edit flag offensive delete link more



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


pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-01-26 07:38:12 +0100 )edit

+1 to @pierre-yves samyn; 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).

Mike Kaganski gravatar imageMike Kaganski ( 2018-07-26 05:38:28 +0100 )edit

answered 2018-07-26 11:20:08 +0100

erAck gravatar image

Just adding the standard FAQ for this.

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2018-07-26 11:57:05 +0100 )edit

I only created it half a year ago, so nothing you missed ;-)

erAck gravatar imageerAck ( 2018-07-26 16:14:13 +0100 )edit

answered 2018-07-26 05:10:56 +0100

gsk gravatar image

updated 2018-07-26 05:11:27 +0100

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

edit flag offensive delete link more


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

Mike Kaganski gravatar imageMike Kaganski ( 2018-07-26 05:49:02 +0100 )edit

Yes Possible.

gsk gravatar imagegsk ( 2018-07-26 06:55:11 +0100 )edit

answered 2017-01-26 00:58:13 +0100

robleyd gravatar image

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
edit flag offensive delete link more

answered 2018-07-26 05:34:57 +0100

updated 2018-07-26 06:03:09 +0100

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 DataText to Columns. One selects (a part of) a column, and starts the tool:

image description

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools



Asked: 2017-01-26 00:07:15 +0100

Seen: 19,611 times

Last updated: Jul 26 '18