[Solved] Last location of a character in a string

[RusselB]

In an updated list I have found that some of the entries have characters (eg: \) multiple times in the entry.
I can, thanks to an earlier post, easily determine how many of these characters are in the entry, and using FIND or SEARCH allows me to find the first occurrence of the character... this is fine for most of the entries where the character only occurs once.
However, there are entries where the character occurs more than once, so I'm looking for a way to locate the location of the last occurrence.... or a way of getting the first occurrence working from right to left.
An example of one of these entries is

Users\Russel\Music\K\Korn\KORN - DISCOGRAPHY (1994-13)\[1998] Follow The Leader (2 CD)\[CD 2] Follow The Leader (All In The Family)\01 - All In The Family (Clark World Mix).mp3 287164 0 (4:47)

I could, of course, use multiple helper columns to go through the entry one character (in this case the \) at a time, but I'm hoping there's an alternative.
P.S.: If this can be done, but requires a macro, assistance with installing/saving/usage of the macro will be required.

[gerard24]

Code: Select all

=SEARCH("\\[^\\]+$";A1)

\ is a REGEX, so I write him twice.
For searching another character like "µ", the formula becomes:

Code: Select all

=SEARCH("µ[^µ]+$";A1)

search for "µ" SEARCH("µ
not followed by another "µ" [^µ]
untill the end $.

[Villeroy]

=SEARCH(A1;B1)
with your text in B1 and regex \\[^\\]*$ in A1 searching for a literal backslash \\ followed by any amount of non-backslashes [^\\]* until the end of string $

[gerard24]

First occurence of \ from right to left can also be found without REGEX. (turn off regex in options)
Arry formula:

Code: Select all

=MATCH("\";MID(A1;LEN(A1)+1-ROW(INDIRECT("A1:A"&LEN(A1)));1);0)

MID extract characters one by one in reverse order (using LEN+1-{1;2;3;...;LEN}) and returns an arry of string.
MATCH returns the position of "\" in this array.

[RusselB]

Thanks to all.
@Gerard24, your regex solution seems to work the best and fastest.. there was a noticeable time discrepancy when using the non-regex formula.
@Villeroy: I'm not sure why, but your suggestion keeps bringing up #VALUE!
Is there a special way I have to enter the regex code into the cell A1? I tried doing it just by copy & pasting from here (and a slight adjustment to get the column letters to correspond).

[Villeroy]

You've got to enable regular expressions in the calculation options.

[RusselB]

If they weren't enabled, then Gerard24's regex wouldn't have worked, but it does.
The main difference between his and yours, is that, with his, the search parameter is hard coded to the formula, as, with yours, it's done as a cell reference.

[Villeroy]

Gerards super smart array formula does not use any regex. It dissembles the string into separate characters in reverse order. Then it uses MATCH to find the first occurrence of \ in the reverse string.
Pattern matching in formulas had been implemented in horrible ways. Calc could be far more "compatible" to all other spreadsheets if it would use ordinary shell pattern matching. Regexes should be implemented by means of a separate function add-in with regex replace, matching arrays and all the flags and features of regular expressions (multi-line matching, non-greedy mode, case insensitive mode etc).

[ken johnson]

Code: Select all

=FIND(CHAR(0);SUBSTITUTE(A1;"\";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))))

is neither regex nor array so its processing time might be somewhere between the two.

Ken Johnson

[RusselB]

@Villeroy: Gerard's first reply, date/timed at Sun Dec 21, 2014 3:39 does use regex. This is the very first reply to my original posting.

[Villeroy]

Negative. I can not confirm that =SEARCH("\\[^\\]+$";A1) works with regex in formulas disabled.
It is the same approach as mine, but it does not match when A1 has a trailing backslash (no non-backslashes between the last backslash and the end of string).

[RusselB]

I never claimed that =SEARCH("\\[^\\]+$";A1) works with regex in formulas disabled.
What I said, or at least meant, was that this code does work while your code, which has the "\\[^\\]*$ in a different cell (and that cell is referenced in the SEARCH) does not work. Thus, to my thinking (and having confirmed), regex is enabled, yet the code you suggested (with the columns changed to match my actual data) returns #VALUE!

[Villeroy]

20141220.ods

Proof of the pudding

(13.38 KiB) Downloaded 258 times

[RusselB]

All I can say is, my apologies.... I still have no idea why, originally, one worked but the other didn't.
After downloading (and opening) your attachment as a brand new spreadsheet, the regex formulas showed #VALUE!
Then I checked the setting for using regular expressions in formulas, to find it was off. Turned it on and the regex show the same results.
It almost seems like that setting is set on a spreadsheet by spreadsheet basis, rather than affecting all of Calc, like the rest of the settings in that area.

[morphingstar]

to Villroy
thanks, your method works fine for my needs. I have rearranged the cells to my preference. I would like to save 1 column. How?
Result attached.

[RusselB]

You might be able to save that column by combining formulas, or just remove the column that contains the =formula() function... all that does is display the formula that is being processed in another cell.
You don't say which tab of your spreadsheet you want to save the column in, so I'm guessing that it's the sheet that contains the formula provided by Villeroy.

As to the confusion tab, with the 14 showing in E14, the formula is wrapped in the ROW function, so the row number that the formula is in is returned.
As the formula is in E14, the row number is 14, which is returned,