OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] Search and replace, regexp to strip numbers

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Grandis
OOo Advocate
OOo Advocate


Joined: 05 Mar 2007
Posts: 232
Location: Norway

PostPosted: Wed May 26, 2010 8:23 am    Post subject: [solved] Search and replace, regexp to strip numbers Reply with quote

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:
Code:
[0-9]+
$0


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 sp3


Last edited by Grandis on Wed May 26, 2010 12:42 pm; edited 1 time in total
Back to top
View user's profile Send private message
stevesaunders
OOo Advocate
OOo Advocate


Joined: 26 Dec 2009
Posts: 408

PostPosted: Wed May 26, 2010 10:10 am    Post subject: String Reply with quote

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
View user's profile Send private message
Grandis
OOo Advocate
OOo Advocate


Joined: 05 Mar 2007
Posts: 232
Location: Norway

PostPosted: Wed May 26, 2010 10:17 am    Post subject: Re: String Reply with quote

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 sp3
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Wed May 26, 2010 11:47 am    Post subject: Reply with quote

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).
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
Grandis
OOo Advocate
OOo Advocate


Joined: 05 Mar 2007
Posts: 232
Location: Norway

PostPosted: Wed May 26, 2010 12:42 pm    Post subject: Reply with quote

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 Very Happy
_________________
Windows XP sp3
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Wed May 26, 2010 11:47 pm    Post subject: Reply with quote

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
View user's profile Send private message
Grandis
OOo Advocate
OOo Advocate


Joined: 05 Mar 2007
Posts: 232
Location: Norway

PostPosted: Thu May 27, 2010 12:08 am    Post subject: Reply with quote

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 sp3
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Thu May 27, 2010 6:12 am    Post subject: Reply with quote

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
View user's profile Send private message
Grandis
OOo Advocate
OOo Advocate


Joined: 05 Mar 2007
Posts: 232
Location: Norway

PostPosted: Fri May 28, 2010 6:33 am    Post subject: Reply with quote

Thank you for a excelent explanation keme. I got it now Very Happy
_________________
Windows XP sp3
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group