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.
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.
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
Otherwise you can use
Edit -> Find & Replace - by using the following procedure:
- Select your column
Edit -> Find & Replace
Other Options 1:
[x] Current selection only
Other Options 2:
[x] Regular expressions
Tested using LibreOffice:
Version: 220.127.116.11; 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 () next to the answer.
See this FAQ.
It worked! Thank you!