Apache OpenOffice Community Forum

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.

Use the Find & replace feature with Regular expression option.

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

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.

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 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.

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.

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.

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

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!

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+

As I wrote above:
see the Help:

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

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.