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

right find (find last occurrence of text)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jantman
General User
General User


Joined: 23 Jul 2006
Posts: 7

PostPosted: Fri May 30, 2008 7:06 am    Post subject: right find (find last occurrence of text) Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 30, 2008 7:16 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Fri May 30, 2008 7:26 am    Post subject: Reply with quote

Dragging the lower right corner of A1 to B1 will also accomplish this.
Back to top
View user's profile Send private message
jantman
General User
General User


Joined: 23 Jul 2006
Posts: 7

PostPosted: Fri May 30, 2008 7:33 am    Post subject: Reply with quote

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
View user's profile Send private message
jantman
General User
General User


Joined: 23 Jul 2006
Posts: 7

PostPosted: Fri May 30, 2008 7:34 am    Post subject: Reply with quote

JohnV - I don't know if there's a preferences setting I missed, but dragging just copies it for me.
Back to top
View user's profile Send private message
jantman
General User
General User


Joined: 23 Jul 2006
Posts: 7

PostPosted: Fri May 30, 2008 7:42 am    Post subject: Reply with quote

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
View user's profile Send private message
jantman
General User
General User


Joined: 23 Jul 2006
Posts: 7

PostPosted: Fri May 30, 2008 7:56 am    Post subject: Reply with quote

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


Joined: 01 Apr 2004
Posts: 413

PostPosted: Mon Jun 02, 2008 3:01 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Mon Jun 02, 2008 7:46 am    Post subject: Reply with quote

For this specific problem:
=REPLACE(A1;LEN(A1)-2;3;MID(A1;LEN(A1)-2;3)+1)
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