| View previous topic :: View next topic |
| Author |
Message |
jantman
General User
Joined: 23 Jul 2006
Posts: 7
|
 Posted: Fri May 30, 2008 7:06 am Post subject: right find (find last occurrence of text) |
 |
|
I've looked through the text functions, but can't seem to find a function for finding the LAST occurrence of text (even though it's a standard function in Java and most other languages).
Specifically, I'm trying to make an easy-to-copy formula for generating a series of IP addresses, i.e.
in the first cell I type aaa.bbb.ccc.123, and put a formula in the second cell that finds the last ".", and concatenates everything up to and including that (aaa.bbb.ccc.) with the value after it (123) plus one, so I end up with "aaa.bbb.ccc.124".
Thanks for any help.
-Jason |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri May 30, 2008 7:16 am Post subject: |
|
|
A regex can do that:
B1 =SEARCH("([:digit:]\.){3}";A1)
C1 =MID(A1;B1+1;LEN(A1)) [lenght argument *not* optional]
or
C1 =RIGHT(A1;LEN(A1)-C1)
[untested]
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8976
Location: Lexinton, Kentucky, USA
|
| Posted: Fri May 30, 2008 7:26 am Post subject: |
|
|
| Dragging the lower right corner of A1 to B1 will also accomplish this. |
|
| Back to top |
|
|
jantman
General User
Joined: 23 Jul 2006
Posts: 7
|
| Posted: Fri May 30, 2008 7:33 am Post subject: |
|
|
| Hmm... I've never worked with regex in OO before (but I have in most programming languages). I'm getting a value error (that wonderful "#VALUE!") when I try that... and I did change A1 to the correct cell.... |
|
| Back to top |
|
|
jantman
General User
Joined: 23 Jul 2006
Posts: 7
|
| Posted: Fri May 30, 2008 7:34 am Post subject: |
|
|
| JohnV - I don't know if there's a preferences setting I missed, but dragging just copies it for me. |
|
| Back to top |
|
|
jantman
General User
Joined: 23 Jul 2006
Posts: 7
|
| Posted: Fri May 30, 2008 7:42 am Post subject: |
|
|
No idea why I got the value error, but I'm not afraid of a kludge in OO, so I changed that to:
| Code: |
=SEARCH("([0-9]+\.){3}";A1)
|
Now it works, but it's returning 1, so it's not much help in finding the last period. |
|
| Back to top |
|
|
jantman
General User
Joined: 23 Jul 2006
Posts: 7
|
| Posted: Fri May 30, 2008 7:56 am Post subject: |
|
|
Ok. Thanks to all for the assistance. Giving a little thought to non-OO experience with I found the problem:
1) I don't know when the POSIX character classes were introduced, but my OO 2.3 gives a value error if I just search for "[:digit:]".
2) To match the position of the last "." followed by digits, I used | Code: | | SEARCH("\.[0-9]+$";A1) |
So...
To get the first 3 octets with a trailing period (up to the last ".", inclusive)
| Code: | | =LEFT(A1;SEARCH("\.[0-9]+$";A1)) |
To get the last octet:
| Code: | | =RIGHT(A1;LEN(A1)-SEARCH("\.[0-9]+$";A1)) |
So, finally:
| Code: | | =CONCATENATE(LEFT(H30;SEARCH("\.[0-9]+$";H30));VALUE(RIGHT(H30;LEN(H30)-SEARCH("\.[0-9]+$";H30)))+1) |
|
|
| Back to top |
|
|
davidh182
OOo Advocate
Joined: 01 Apr 2004
Posts: 413
|
| Posted: Mon Jun 02, 2008 3:01 am Post subject: |
|
|
John V's solution works for me if you precede the IP number with a space (you can remove this later) - otherwise it increments the first number aaa.
Here is a more general findright function:
| Code: |
function findright(find_text as String, within_text as String)
findright=ERROR(12)
for i=1 to len(within_text)-len(find_text)+1
if StrComp(mid(within_text,i,len(find_text)),find_text)=0 then findright=i
next i
end function
|
|
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8976
Location: Lexinton, Kentucky, USA
|
| Posted: Mon Jun 02, 2008 7:46 am Post subject: |
|
|
For this specific problem:
=REPLACE(A1;LEN(A1)-2;3;MID(A1;LEN(A1)-2;3)+1) |
|
| Back to top |
|
|
|
