How to delete a number and a period in cells?

Discuss the spreadsheet application

How to delete a number and a period in cells?

Postby averagejoe8 » Tue Dec 11, 2018 8:45 pm

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
averagejoe8
 
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Postby Zizi64 » Tue Dec 11, 2018 9:09 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Zizi64 » Tue Dec 11, 2018 9:12 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby RusselB » Tue Dec 11, 2018 9:24 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5702
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby averagejoe8 » Tue Dec 11, 2018 9:36 pm

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
averagejoe8
 
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Postby RusselB » Tue Dec 11, 2018 9:52 pm

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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5702
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby averagejoe8 » Tue Dec 11, 2018 9:56 pm

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
averagejoe8
 
Posts: 5
Joined: Tue Dec 11, 2018 8:31 pm

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

Postby Zizi64 » Tue Dec 11, 2018 9:58 pm

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
Last edited by Zizi64 on Tue Dec 11, 2018 10:01 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby averagejoe8 » Tue Dec 11, 2018 10:00 pm

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!
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?

Postby averagejoe8 » Tue Dec 11, 2018 10:03 pm

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+
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?

Postby Zizi64 » Tue Dec 11, 2018 10:15 pm

As I wrote above:
see the Help:

[:digit:] Represents a decimal digit. Use [:digit:]+ to find one or more of them.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8555
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby RusselB » Tue Dec 11, 2018 11:20 pm

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 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5702
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 10 guests