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

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

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

This worked for me. It removed the leading ’

Working thanks.

“Search KEY not found”

Make sure Regular expressions is ticked.

You can instead in LO 6 and above, select the column and click Data > Text to columns untick any separators and click OK.

Cheers, Al

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:

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.

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.

It does. Re-evalute a value. Use regex, find all (.*), replace with &, which is the value itself.