How to get the last search string in text field?

naruemon · June 19, 2012 6:19AM

How to get the last position of specific word in the content?
SEARCH() function always return the first position.
Please kindly suggest. Thanks ^^

Ex:
"apple egg apple egg apple egg apple good"
..................................................^..... How to get this position (last "apple")?

Soruban · June 19, 2012 7:10AM

Hi,

You can do that with use some javascript in a formula as follows:

SCRIPT(`
	var index = names.lastIndexOf("apple");
	if(index >= 0) index++; // We increase the index to get a number starting from 1.
	index;
`, "names", [Field 1])

I have attached a file with this example.

mustafa · June 19, 2012 7:19AM

Alternatively, you can use the following formula:


/*Declare the value and the field to search, in this case, searching for apple and Field 1*/
DECLARE(valueToSearch, "apple", fieldToSearch, [Field 1], 

/*Store the result in the variable called "index". This uses a SEARCHREGEX function to find the index that doesn't have another occurrence of the search string anywhere after the string, in other words it looks for index of apple, only if there is no other words apple till the end of the string. */
DECLARE(index, SEARCHREGEX(valueToSearch+"((?!"+valueToSearch+").)*$", fieldToSearch, 1),  index))

naruemon · June 19, 2012 9:10AM

Many thanks for suggestion ^_^

Welcome!

Categories

Ideas Parade