How to find last occurrence of character in string?

Discuss the spreadsheet application

How to find last occurrence of character in string?

Postby mathman99 » Fri Jul 03, 2009 8:47 am

All,

Is it possible to locate the last occurrence of a particular character in a string,
An example : if the string was "C:\Program Files\GIMP-2.0\etc\fonts", how would you find the position of the last "\" ?
Is it possible to do it purely with functions, i.e. without going into OO Basic ?

regards,
Andrew
OOo 3.1.X on Ms Windows XP
mathman99
 
Posts: 5
Joined: Wed Jul 01, 2009 11:34 am

Re: How to find last occurrence of character in string?

Postby Robert Tucker » Fri Jul 03, 2009 10:44 am

The regex:

Code: Select all   Expand viewCollapse view
(\\)[^\\]*$

will find "\fonts"

Search for:

Code: Select all   Expand viewCollapse view
(\\)([^\\]*$)

Replace with:

Code: Select all   Expand viewCollapse view
/$2

will replace the "\" with "/" for example.
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
User avatar
Robert Tucker
Volunteer
 
Posts: 1230
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

Re: How to find last occurrence of character in string?

Postby acknak » Fri Jul 03, 2009 5:08 pm

Or, something like =SEARCH("\\[^\\]*$"; B2) if you need the position as a number value.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17403
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: How to find last occurrence of character in string?

Postby mathman99 » Fri Jul 03, 2009 11:28 pm

Thanks for this everyone, but could someone please break this regular expression down. It is making my head spin.
Also, acknak's version is different than Robert Tucker's, in that the first \\ does not have round brackets around it.
OOo 3.1.X on Ms Windows XP
mathman99
 
Posts: 5
Joined: Wed Jul 01, 2009 11:34 am

Re: How to find last occurrence of character in string?

Postby acknak » Sat Jul 04, 2009 12:14 am

Have you looked at this? Regular Expressions in Calc [OOo wiki]

Ignore the differences--they're beside the point. The parentheses are only needed if you're using Find & Replace, so I left them out. Robert's regex would work just the same if you used it in the SEARCH example.

Walk through it one element at a time:
    \\ A single, literal backslash character. You have to use two in a row because the backslash is a special character in regular expressions; two in a row means a plain, non-special backslash character.

    [^\\] A class (or set) of characters, any of which will count as a match. In this case, the set has only one character, our literal backslash friend again. The caret ^ causes the meaning of the set to be inverted: instead of matching any character in the class, it matches any character not in the class. So this means: "match any character other than a backslash".

    * This is a repetition marker; it means the preceding pattern element should match zero or more times. So that's any character other than a backslash, any number of times, including none at all.

    $ This matches only at the end of the input.
So all together, it matches a backslash, followed by any number of non-backslashes, followed by the end of the input. That's a long way of saying "the last backslash in the input".
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17403
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests