How to delete a number and a period in cells?
-
- Posts: 5
- Joined: Tue Dec 11, 2018 8:31 pm
How to delete a number and a period in cells?
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.
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
Re: How to delete a number and a period in cells?
Use the Find & replace feature with Regular expression option.
You can search the [number] and the "dot" character at the beginning of the "paragraph".
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.
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.
Re: How to delete a number and a period in cells?
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.
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.
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.
Re: How to delete a number and a period in cells?
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
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.
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))
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.
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.
-
- Posts: 5
- Joined: Tue Dec 11, 2018 8:31 pm
Re: How to delete a number and a period in cells?
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.
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
Re: How to delete a number and a period in cells?
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.
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.
-
- Posts: 5
- Joined: Tue Dec 11, 2018 8:31 pm
Re: How to delete a number and a period in cells?
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.
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
Re: How to delete a number and a period in cells?
F&R with regular expression option:
To search:
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
To search:
Code: Select all
^[:digit:]\.
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.
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.
-
- Posts: 5
- Joined: Tue Dec 11, 2018 8:31 pm
Re: How to delete a number and a period in cells?
Zizi64 wrote:F&R with regular expression option:
To search:There is a space at the end of the "search" string.)Code: Select all
^[:digit:]\.
To replace: nothing
Thanks, that's what I was looking for I really appreciate it!
OpenOffice 4.1.5 on Windows 10
-
- Posts: 5
- Joined: Tue Dec 11, 2018 8:31 pm
Re: How to delete a number and a period in cells?
Zizi it only worked up to the number 9.averagejoe8 wrote:Zizi64 wrote:F&R with regular expression option:
To search:There is a space at the end of the "search" string.)Code: Select all
^[:digit:]\.
To replace: nothing
Thanks, that's what I was looking for I really appreciate it!
The numbers and periods are still there for 10+
OpenOffice 4.1.5 on Windows 10
Re: How to delete a number and a period in cells?
As I wrote above:
see the Help:
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.
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.
Re: How to delete a number and a period in cells?
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.
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.
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.