How to delete a number and a period in cells?

Discuss the spreadsheet application
Post Reply
averagejoe8
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

How to delete a number and a period in cells?

Post 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.
OpenOffice 4.1.5 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post 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".
Last edited by Zizi64 on Tue Dec 11, 2018 9:12 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post 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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Post 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

=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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
averagejoe8
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Post 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.
OpenOffice 4.1.5 on Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Post 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
averagejoe8
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Post 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.
OpenOffice 4.1.5 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

F&R with regular expression option:

To search:

Code: Select all

^[: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
Last edited by Zizi64 on Tue Dec 11, 2018 10:01 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
averagejoe8
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Post by averagejoe8 »

Zizi64 wrote:F&R with regular expression option:

To search:

Code: Select all

^[: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!
OpenOffice 4.1.5 on Windows 10
averagejoe8
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Post by averagejoe8 »

averagejoe8 wrote:
Zizi64 wrote:F&R with regular expression option:

To search:

Code: Select all

^[: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+
OpenOffice 4.1.5 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Post by Zizi64 »

As I wrote above:
see the Help:
[:digit:] Represents a decimal digit. Use [:digit:]+ to find one or more of them.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Post 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply