Page 1 of 1

How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 8:45 pm
by averagejoe8
Hey everyone,

I'm sure the solution is very simple and I did try to find an answer, but I didn't know exactly how to look and what I did look for didn't help.

I have a spreadsheet that has number and a period at the start of the data that I would like to delete from each cell down the column. To keep it simple, I'll give a simple example as a shopping list:

1. 2 heads of lettuce
2. 1 Tomato
3. Kale
4. 4 Onions

I would like to get rid of the number and the period in each cell down the column (Please note, the numbers and periods are in the cells, those numbers aren't the row numbers on the side of the spreadsheet.) But not delete any other numbers in the cell. Just everything before the period. I don't have any other periods in the important data so deleting everything before and including the
period won't affect the data.

So it would just look like this:

2 heads of Lettuce
1 Tomato
Kale
4 Onions

I know it's probably a very simple formula and I appreciate the help in advance.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:09 pm
by Zizi64
Use the Find & replace feature with Regular expression option.

You can search the [number] and the "dot" character at the beginning of the "paragraph".

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:12 pm
by Zizi64
Or select all of the data,
Ctrl-X,
Shift-Ctrl-V (on same place), use the "dot" character as data separator in the import filter.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:24 pm
by RusselB
Welcome to the Forums.
I had to experiment with a few ideas to ensure that I gave you code that works.
My suggestion, and this will require an additional column, is to use
Code: Select all   Expand viewCollapse view
=MID(A1;FIND(". ";A1;1)+2;LEN(A1))

This presumes that your data is in column A and starts in the first row. If it doesn't, then you'll have to change each of the A1 in the code to match the cell address for your first item.... or you could just paste the code into B1 then copy it to where you need it, thus allowing Calc to make the appropriate changes automatically.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:36 pm
by averagejoe8
I was hoping there was a simple find and replace function.

Delete everything before and including the period.

Zizi I'm not really sure how to do what you're saying.

Thanks.

Thanks so much for the quick replies.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:52 pm
by RusselB
Zizi's second suggestion using the cut and paste special commands is the simplest method suggested, especially since your latest post indicates that you were wanting a find & replace, rather than a formula, as you specified in the first post.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:56 pm
by averagejoe8
Russel,

I tried your formula but I'm getting an error 522.

Yes the data starts on A1

It can be a formula or a find and replace, but I don't understand what exactly to type into the find box as Zizi suggested. I tried using [number] and "dot" in the find box.

Thanks.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 9:58 pm
by Zizi64
F&R with regular expression option:

To search:
Code: Select all   Expand viewCollapse view
^[:digit:]\.

There is a space at the end of the "search" string.)

To replace: nothing


See the "List of the regular expressions" in the Help:
https://help.libreoffice.org/Common/Lis ... xpressions

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 10:00 pm
by averagejoe8
Zizi64 wrote:F&R with regular expression option:

To search:
Code: Select all   Expand viewCollapse view
^[:digit:]\.

There is a space at the end of the "search" string.)

To replace: nothing



Thanks, that's what I was looking for I really appreciate it!

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 10:03 pm
by averagejoe8
averagejoe8 wrote:
Zizi64 wrote:F&R with regular expression option:

To search:
Code: Select all   Expand viewCollapse view
^[:digit:]\.

There is a space at the end of the "search" string.)

To replace: nothing



Thanks, that's what I was looking for I really appreciate it!


Zizi it only worked up to the number 9.

The numbers and periods are still there for 10+

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 10:15 pm
by Zizi64
As I wrote above:
see the Help:

[:digit:] Represents a decimal digit. Use [:digit:]+ to find one or more of them.

Re: How to delete a number and a period in cells?

PostPosted: Tue Dec 11, 2018 11:20 pm
by RusselB
The error you are getting with the formula I suggested, which REQUIRES an extra column (you can't have data and a formula in the same cell), indicates that you are trying to put it in the same cell as your data.. thus the formula tries to use the information in A1, which is where the formula exists... thus causing a circular reference... If you put the formula into B1 it (should) work fine.
Zizi's F&R suggestion changes your actual data.