Ask Your Question
-1

How to find and replace just the negative numbers in a columns in Calc?

asked 2016-09-03 10:06:55 +0100

Fersal gravatar image

In LibreOffie Calc I have a column with numbers with positive and negative signs, I want to remove just the ones with negative signs, I have tried to do it as I would do with Excel (see below) but it doesn't work, I have tried to use regular expressions but nothing of this works. In addition if I try to replace a single number by other just in column, it do it in all the columns of the sheet. Why is so complicated to use this software? I am tried to find a good alternative to MS Office. Plese help, I just waste about 1.5 hours in this which in Excel I would have taken 10 seconds.

search for: -*

Replace with:

Thanks

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2016-09-03 20:19:05 +0100

Fersal gravatar image

Hi, thank you for your replies and comments, the filtering option effectively works, but I have other data sets which are really big so in those cases filtering is not a very practice option. I just thought there was an option as easy as use find&replace (like the one in Excel). Also I have a problem with option find&replace, when I try to replace any character from a specific column (previously selected) it replace not only the characters in that selected column but in all the columns of the sheet that match the character I want to replace. I'm using Linux Mint 17 and OpenOffice 5.

By the other hand I apologize if my question bothers someone, I had not that intention. probably floris is right that I have to start reading the documentation of Calc and start from zero because I don't believe that my skills in in using Excel will be useful with Calc. the problem is that several times one doesn't have the time to read the documentation in the precise moment in which one need to use an specific option of the software, that is why one seeks help in the forums. Thank for your feedback.

edit flag offensive delete link more
0

answered 2016-09-03 11:49:30 +0100

floris v gravatar image

updated 2016-09-03 22:08:31 +0100

Do you only want to remove the minus signs or do you want to remove all rows with numbers with minus signs? In the latter case, use a filter (Data - Filter etc) to select all rows with a minus sign, then delete all of them, then remove the filter.

EDIT: If you just want to delete the negative numbers in one column:

  1. select the column,

  2. in Find/Replace, type ^-.*$ in the Search box and leave the Replace box blank.

  3. Tick Other options and tick Regular expressions (in the middle) and Current selection only (probably already ticked), then click Replace all. The rest should be obvious.

Not meaning to be condescending , but you started using new software. Learning to get to work with something new always takes some time. You need to read the documentation, get comfortable with the user interface, find where the program differs from what you were used to, etc.

edit flag offensive delete link more

Comments

I'm having the same issue and tried your solution without success.

nycgeordie gravatar imagenycgeordie ( 2017-06-24 17:28:20 +0100 )edit
0

answered 2016-09-03 10:59:06 +0100

Zeca gravatar image

updated 2016-09-03 16:32:13 +0100

Go to EDIT -> FIND & REPLACE and then on

Search For: -

Replace With:

If you want to just remove the negative numbers and replace them with empty space why not use a simple IF statement like =IF(A4<0,"",A4) ?

Hope that helped.

edit flag offensive delete link more

Comments

Absolutely not, it just replace the "-" sign. What I want is to replace all the negative numbers.

-60.50 -30.40 50.00 -35.45 -24.34

Eg. I just want to keep the 50.00 from the last column.

Thank you

Fersal gravatar imageFersal ( 2016-09-03 11:45:20 +0100 )edit

I hope that you are aware that as you didn't post a sample, this comment makes absolutely no sense to anyone reading this. You ask help from people you don't know and who don't know you, who will spend some of their time to help you. Do them a favor and put some effort in how you ask your question.

floris v gravatar imagefloris v ( 2016-09-03 11:52:07 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-03 10:06:55 +0100

Seen: 2,719 times

Last updated: Sep 03 '16