How to find last occurrence of character in string?

Discuss the spreadsheet application
Post Reply
mathman99
Posts: 5
Joined: Wed Jul 01, 2009 11:34 am

How to find last occurrence of character in string?

Post by mathman99 »

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
User avatar
Robert Tucker
Volunteer
Posts: 1250
Joined: Mon Oct 08, 2007 1:34 am
Location: Manchester UK

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

Post by Robert Tucker »

The regex:

Code: Select all

(\\)[^\\]*$
will find "\fonts"

Search for:

Code: Select all

(\\)([^\\]*$)
Replace with:

Code: Select all

/$2
will replace the "\" with "/" for example.
LibreOffice 7.x.x on Arch and Fedora.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Post by acknak »

Or, something like =SEARCH("\\[^\\]*$"; B2) if you need the position as a number value.
AOO4/LO5 • Linux • Fedora 23
mathman99
Posts: 5
Joined: Wed Jul 01, 2009 11:34 am

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

Post by mathman99 »

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
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Post by acknak »

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".
AOO4/LO5 • Linux • Fedora 23
Post Reply