how to return result of regular expression (regex)

[JEKelly]

hello.

i am trying to extract (by using formula's in calc) a regular expression that basically looks for a letter followed by a variable amount of digits, e.g. R4322, X616, T88993, etc.

I have successfully used SEARCH with a regex to locate the first character of the string. The problem is that i don't know how long that string is, nor can i return the result of the found string (as far as i know).

i have also tried searching for the character following the regular expression by searching from the beginning of the found expression and looking for a space. But, i run into a problem when the regex string appears at the end of the string being searched, (thereby ending up with one character short).

example:
string, B2: "Intel Xeon X5492 @ 3.40GHz"
searching for "X5492"
formula: SEARCH("([:alpha:]?[:digit:]{3,5}[:space:]?)";B2)

this gives me the the position of the string "X5492", but how do i know how long it is so that i can do a MID statement to extract it?

I know that you can refer to the result of the searched string in the FIND/REPLACE dialog box, but how to do this in a cell's formula?

any ideas?

thanks, in advance.

J

[Robert Tucker]

Can you not:

=SEARCH(("[:alpha:][0-9]+");B2)

in one cell,

=REPLACE(B2;1;SEARCH(("[:alpha:][0-9]+");B2);"")

in another cell, then search for the first space in the second cell etc etc...
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)

[keme]

First, like Robert Tucker indicates without explicitly stating it, forget the question marks. They make the initial letter and the trailing word delimiter optional, but I believe they're required.

Second: I believe you need multiple cells to do this reliably. (You can make it a one-liner, but that would lead to unnecessary repeated calculations and a long and convoluted formula.)

I guess your strings could have spaces or any kind of punctuation character on either side of the code you're after, or nothing (the string starts or ends with the code). Looks like you're limiting to 3, 4 or 5 digits after the letter. So you want a letter starting a "word", immediately followed by 3-5 digits finishing a "word". In OOo, regex for word start/end are \< and \>. So you want to find \<[:alpha:][:digit:]{3,5}\>

In C2, enter =SEARCH("\<[:alpha:][:digit:]{3,5}\>";B2)

Next, you want to find the position of the last character. As the transition from letters to numbers is taken as a word limit, you need to start that search within the row of digits. The digits starts at the next position, C2+1, so we need to find a "word end" after C2+1.

In D2, enter =SEARCH(".\>";B2;C2+1)

Now we know where the code starts and ends, so we can extract it:

In E2, enter =MID(B2;C2;D2-C2+1)

If there's no matching code, C2 will return an error. You may want to catch that in E2, with something like =IF(ISERROR(C2;"** no match **";MID(B2;C2;D2-C2+1)) instead.

If you can have multiple codes inside a string, you need to repeat those 3 cells, starting the search after the point where the previous code ended.

[porg]

My application:
I have a list of domains and want to extract their domain ending with a formula,
so that I can then easily use this retrieved data in the column "TLD" for sorting!

[mclaudt]

Same issue here, but any direct solution yet.

http://user.services.openoffice.org/en/forum/viewtopic.php?t=30502
http://www.oooforum.org/forum/viewtopic.phtml?p=373845

[mclaudt]

Please vote Issue 106099 for adding regexp functionality to SUBSTITUTE function.

It can solve all discussed problems.

[mclaudt]

Solved