| View previous topic :: View next topic |
| Author |
Message |
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
 Posted: Wed May 26, 2010 8:23 am Post subject: [solved] Search and replace, regexp to strip numbers |
 |
|
Hi.
I have a coloumn containing numbers that is pasted from a pdf (copy-paste) file.
However, this cells contains not only numbers, but also spaces and other non-numeric characters.
Therefore I'm trying to use search and replace with regexp to make the search leave all numbers while other characters is removed from cell.
Here is search expression and replace expression:
As far I can understand from the help file, this $0 means that any numbers in the text shall remain in the cell.
PROBLEM: Nothing happens. Cell contents is still the same after I do a find/replace.
I've also tried to insert some test characters in the replace-feld just to see what happens. If I insert "YY" the replace-feild, actually all numbers found where replaced by "YY" - While the unwanted characters remains there (I expected this to happend).
Does this means that the code $0 doesn't work for replace field when regexp is used? Or is there something I obviously didn't get right?
Any ideas?
System:
Open Office 3.2.1
OOO320m17 (build:9498)
OS = XP sp2, norwegian
_________________
Windows XP sp2
Last edited by Grandis on Wed May 26, 2010 12:42 pm; edited 1 time in total |
|
| Back to top |
|
 |
stevesaunders
OOo Advocate
Joined: 26 Dec 2009
Posts: 408
|
| Posted: Wed May 26, 2010 10:10 am Post subject: String |
|
|
If the numbers are all the same length & always in the same place in the string; Yes
=LEFT()
+RIGHT()
=MID()
_________________
Steve ~ Tennessee USA
Mac OS X 10.6.2 Open Office 3.1 |
|
| Back to top |
|
|
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
| Posted: Wed May 26, 2010 10:17 am Post subject: Re: String |
|
|
| stevesaunders wrote: | If the numbers are all the same length & always in the same place in the string; Yes
=LEFT()
+RIGHT()
=MID() |
No. There number consists of one to three digit and text around is random and random length.
The issue is related to regular expression. Im interresting to know if the (replace with) $0 really works.
Nice if anybody can confirm that this work or if this doesn't work.
_________________
Windows XP sp2 |
|
| Back to top |
|
|
Robert Tucker
Moderator
Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK
|
| Posted: Wed May 26, 2010 11:47 am Post subject: |
|
|
Search for:
[^0-9]*([0-9]+)[^0-9]*
Replace with:
$1
seems to work. So does searching for [0-9] and replacing with nothing (providing you don't need to worry about numbers standing by themselves).
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot) |
|
| Back to top |
|
|
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
| Posted: Wed May 26, 2010 12:42 pm Post subject: |
|
|
| Robert Tucker wrote: | Search for:
[^0-9]*([0-9]+)[^0-9]*
Replace with:
$1
seems to work. So does searching for [0-9] and replacing with nothing (providing you don't need to worry about numbers standing by themselves). |
That one was nice. The solution for me. Thank you 
_________________
Windows XP sp2 |
|
| Back to top |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2732
Location: Egersund, Norway
|
| Posted: Wed May 26, 2010 11:47 pm Post subject: |
|
|
For the record, using $0 will insert the entire matching string from the search, so using only $0 in the replace with field should appear to do nothing (because it only replaces the matched content, in this case the string of digits, leaving everything else intact).
$1 (which Robert Tucker used) will insert the part of the match that's specified inside the first set of parentheses. |
|
| Back to top |
|
|
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
| Posted: Thu May 27, 2010 12:08 am Post subject: |
|
|
| keme wrote: | | For the record, using $0 will insert the entire matching string from the search, so using only $0 in the replace with field should appear to do nothing (because it only replaces the matched content, in this case the string of digits, leaving everything else intact). |
Hi. I'm not sure I really understood that concept. Is there an example somewhere on using the $0 replacement?
_________________
Windows XP sp2 |
|
| Back to top |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2732
Location: Egersund, Norway
|
| Posted: Thu May 27, 2010 6:12 am Post subject: |
|
|
This example may clarify the use of match references in regular expressions:
Search for: (l)arge|(s)mall (large or small, with initial letter marked for explicit reference.)
substitute with: a $0 one ($1)
This will replace all occurrences of "large" with "a large one (l)", and all occurrences of "small" with "a small one (s)".
The complete match is either "small" or "large", which is referenced by $0 in the replace term.
The first matching parenthesis is either "s" or "l", referenced by $1 in the replace term.
The single character & (ampersand) can be used as a synonym for $0 in this context. |
|
| Back to top |
|
|
Grandis
OOo Advocate
Joined: 05 Mar 2007
Posts: 232
Location: Norway
|
| Posted: Fri May 28, 2010 6:33 am Post subject: |
|
|
Thank you for a excelent explanation keme. I got it now
_________________
Windows XP sp2 |
|
| Back to top |
|
|
|
