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.
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 (
) next to the answer.
3 Likes
It worked! Thank you!