Removing apostrophe in front of NEGATIVE numbers

English
#1

I’ve downloaded a column of figures that LibreOffice thinks are text. I’m following the procedure below to get rid of single quote marks at the beginning of each cell. My problem is that it only works if the numerals form a positive number. If the numerals are preceded by a minus sign, nothing happens. Obviously, I do not understand precisely what the ^[0-9] means or is doing. I’m hoping someone can point me in the right direction. How do I tweak the search term to make this work for negative as well as positive numbers–without having to manually adjust dozens of cells?

  • Choose Edit - Find & Replace
  • In the Search for box, enter ^[0-9]
  • In the Replace with box, enter &
  • Check Regular expressions
  • Check Current selection only
  • Click Replace All

Many thanks,
Jeanne

#2

That is because you are looking for only digits in the Find field, see List of Regular Expressions
.
Better to use the sample Find and Replace in this wiki document, How to convert number text to numeric data

#5

It is a search pattern, wich consists of two parts:
[0-9] will allow to find any digits 0123456789
^ means the following pattern has to start at the beginning of the cell. Therefore -12 is not found, as the cell starts not with a digit, but with -
I guess you omitted a * or + at the end, wich would find several digits in sequence.
Also not recognized in your pattern is . and , like 1,000.00 or 1.000,00 in my country…
.
If you read the FAQ (linked above by @EarnestAl you find they recommend simply .+ because it is not important to match the figure perfectly. This pattern simply finds all strings in your search range and the replace triggers the automatic recognition of numbers done also by Calc, when you type something in a cell, wich is not defined/formatted as text.
.
While the FAQ is useful, avoid this situation: Usually when importing data you pass a dialogue with settings. There you can tell Calc to import numbers, to detect special numbers (like currencies $4.56) and you can change the type of any column, if necessary.

1 Like
#6

It would be interesting to know the circumstances that result in a successful import of positive figures while failing with negative ones. Do you import from text file (csv)? Please open the csv with Writer or any kind of text editor (Notepad) and show us some positive and negative figures as they are stored in the plain text file.
And tell us your version of LibreOffice, please.

#8

Thanks so much to everyone.

Earnest AI: Thanks for that wiki document. That .+ and $0 did the trick. I was more familiar with the approach I was taking having taken it in the very very distant past, long enough that I didn’t really remember how/why it worked. But .+ and $0 works beautifully.

Villeroy: No, the import was consistent. All cells had the apostrophe. I successfully used the ^[0-9] search term to fix the positive numbers, but I failed with the negative numbers and came here. And yes, your
^-[0-9]
fixed the problem with that search term. (I won’t bore you with why I didn’t figure that out on my own.)

Wanderer: I did try re-importing but I must have done something dumb because I couldn’t find a way to direct LibreOffice in the way I wanted. And yes, your
^-*[0-9]
was the even more elegant solution, doing the whole job in one pass.

FPY: I got “search key not found” with
^-?\d
(with regular expressions checked).
But thanks to you too.

EDIT a few minutes later: PS: I’d like to give both Wanderer and EarnestAI credit for solutions but it doesn’t look like I can. So I think it is going to EarnestAI since he got here first.
But again, thanks to all.

#9

image
image772×503 60.3 KB

#3

Try ^-[0-9]

#4

for negative integer. For positive and negative I’d suggest/try ^-*[0-9]

#7

^-?\d :bulb:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.