escape a single quote (') in INDEX string-function.

[#1]

Hi,

I need to find out a way to identify INDEX of a single-quote, for example in string: Best Squash Player in '1998' is 'XYZ'. I want to find the index of first single-quote (the one just before 1998).

I tried something like this Index('Best Squash Player in ''1998'' is ''XYZ'','''',1). But it results into an error.

Each of the single-quotes is preceded by another single-quote as an escape character.

Thanks.

[anbu1984]

=Index('Best Squash Player in ' & Chr(39) &'1998' & chr(39) & ' is ' & chr(39) & 'XYZ' & chr(39),chr(39),1)

[#3]

Hi Anbu. Thanks for a quick response. But I was wondering if my string was dynamic or stored in a variable,  the above solution may not hold correct. Because in that case I would not be in a position to identify and place chr(39) dynamically.

Sorry, if I missed that part in my question, but it would be best if you put forward a dynamic approach.

[anbu1984 · Accepted solution]

If your input is in variable, then try like this

=Index(vStr,chr(39),1)

EDIT: Value of vStr is Best Squash Player in '1998' is 'XYZ'

You can use quote without chr(39) when you assign string to a variable

[#5]

Awesome. It worked. Thank you!