Apache OpenOffice Community Forum

Hi,

So I have a number of cells in spreadsheet that look like these (each line below is a single cell). They are all in a single column (C)

0130 CVS/PHARMACY #00447 CVS/PHARMACY #00447 NORTHAMPTON MA
0204 PGI*WHATEVER WORKS PGI*WHATEVER WORKS 8777565053 MA
0209 CUP AND TOP CAFE CUP AND TOP CAFE FLORENCE MA

I'm wondering if it's possible to do any of the following things:

1) Automatically search all the cells in column C and remove any number that appears. So the first line above would read like this:

CVS/PHARMACY # CVS/PHARMACY # NORTHAMPTON MA

2) Automatically search the entire spreadsheet and remove any instance of four consecutive numbers, which start with 0. So, the above lines would read like this:

CVS/PHARMACY #00447 CVS/PHARMACY #00447 NORTHAMPTON MA
PGI*WHATEVER WORKS PGI*WHATEVER WORKS 8777565053 MA
CUP AND TOP CAFE CUP AND TOP CAFE FLORENCE MA


That way if I sort by Column C the sort will be more accurate.

Thanks for any help, I realize this is a long shot.

Chris

Remove all numbers (runs of one or more digits):

1) Select the cells you want to change
2) Edit > Find & Replace
Search for: \d+
Replace with: empty
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All

Remove numbers at the start:

1) Select the cells you want to change
2) Edit > Find & Replace
Search for: ^\d+\s*
Replace with: empty
Options/Current selection only: ON
Options/Regular expressions: ON

Click Replace All

Also, you can use the tool Data - Text to Columns in the "Fixed width" mode.
Measure out the first five characters and mark the resulting column "Hide"

Thank you both very much! I used each of your suggestions for different parts of my project and they both saved me a great deal of time.

Many thanks,

fatgreta (Chris)