How do I get rid of the apostrophe before a number in Calc?

As part of our process we download a .xls file from a route planning software. Numbers in the spreadsheet always have an apostrophe before them. Formatting the cells to “numbers” makes no difference. The only way I’ve found that works is to manually delete each apostrophe but there must be a quick way to do this. I’ve tried using find ( .*) and replace (&)but this only seems to work for the first cell in the selection even though I click Replace All. It’s driving me nuts and wasting an awful lot of time.

2 Likes

It works for me for all cells, are you sure you don’t have the first cell selected, check that ‘Current selection only’ is not checked.

Hello,

the apostrophe is an indicator that what is text could be simply converted to a number. Just select your numbers (columns) and Data -> Text To Column and press OK

Otherwise you can use Edit -> Find & Replace - by using the following procedure:

  • Select your column
  • Menu Edit -> Find & Replace
    Find: (.+)
    Replace: $1
    Other Options 1: [x] Current selection only
    Other Options 2: [x] Regular expressions
  • Click Replace All

Tested using LibreOffice:

Version: 6.4.3.2; Build ID: 747b5d0ebf89f41c860ec2a39efd7cb15b54f2d8
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US; Calc: threaded

Hope that helps.

If the answer helped to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

2 Likes

See this FAQ.

1 Like

It worked! Thank you!