LOOKUP function. Need first occurrence from the right, not left

[SEASIA]

Hello everybody. I'm new here and this is my very first post.

My Excel experience is limited but nevertheless created over time several sheets with success. However, now I'm stuck at this.

'Stock Options'!D3 contains .AAPL220729P140

What I need is this: Find the position of the first P from the right, but give out number of characters from the beginning (left) to that P. In this case the correct result would be 11. Not 4.

Those are stock option symbols. If there isn't a P in the stock symbol but only a P for Put my formula works. I'm using this formula:

=IFERROR(LOOKUP(2^15,SEARCH("P",'Stock Options'!D3,ROW(INDIRECT("1:"&LEN('Stock Options'!E3))))), "0")

I added IFERROR because if there is no P the result would be #n/a. But because I'm doing the same for the character C, I then need the function =MAX to determine the higher value. So I replaced #n/a with a 0.

The length of the symbols varies. Otherwise it would be easy when the P is always at the same position. But this is not the case. I hope this is possible without VBA because that would be a complete new world for me.

 

[SEASIA]

btw; It doesn't need to be this LOOKUP formula. It can be whatever works as long the result is correct. Thank you.

 

[SEASIA]

I don't know how to edit a post. Now I tried this formula:
=FIND(CHAR(134),SUBSTITUTE(I16,"P",CHAR(134),(LEN(I16)-LEN(SUBSTITUTE(I16,"P","")))/LEN("P")))
Basically it works. But it brings up another issue. In the case of .AAPL220729P147 the result is 12 which is correct. Not 11 as I wrote in the first post. But if the symbol is .AAPL220729C167.5 the result is 4. But this P should be ignored. The question, how to do it. I need to divide the area where it looks for the P. Any suggestions?

 

[SEASIA]

All the symbols start with a . and maximum 4 characters. Is it possible to to modify the formula posted above to ignore the first 4 characters. Even when the stock symbol is only 1 character, then the date follows. In any way the result would be fine.

I did it this way. Entered =REPLACE('Stock Options'!D3,1,5, "00000") in a cell as reference for the lookup function. It seems to work.

 

[Tom.Jones]

Hi,

But it brings up another issue. In the case of .AAPL220729P147 the result is 12 which is correct

If I understood your question correctly the answer will be 11 not 12

 

[Peter_SSs]

All the symbols start with a . and maximum 4 characters. Is it possible to to modify the formula posted above to ignore the first 4 characters. Even when the stock symbol is only 1 character, then the date follows. In any way the result would be fine.

I did it this way. Entered =REPLACE('Stock Options'!D3,1,5, "00000") in a cell as reference for the lookup function. It seems to work.

Wouldn't this work for you directly?

=IFERROR(FIND("P",'Stock Options'!D3,6),0)

 

[SEASIA]

Hi,



If I understood your question correctly the answer will be 11 not 12

It's 12 because the symbol starts with a dot

 

[SEASIA]

Wouldn't this work for you directly?

=IFERROR(FIND("P",'Stock Options'!D3,6),0)

Thank you. Seems to work. But now, I spent so many hours in the entire Workbook with several sheets, I won't change anything lol. It works like a clockwork. The most cells are =RTD functions. So the sheets are very dynamic.

OT: Another question: Microsoft doesn't list Excel for android as client fort RTD function. However, is there a workaround to get the data on the mobile phone? If I runf the excel workbook on my Windows 10 machine, the the program from which RTD gets the data needs to be running. In this case it's thinkorswim. Wouldn't it be possible to link a workbook on mobile phone to the one on the PC and check for updates?

Btw, isn't it possible to edit posts

 

[Peter_SSs]

Thank you. Seems to work.

You're welcome.

I cannot answer your question about data to phone but in relation to editing posts, until you have made a few legitimate posts you cannot edit at all (anti-spam measure). However, you should now be able to edit your own posts - but only for 10 minutes after making the post. Next time you post, look below the bottom left of your post (within 10 minutes) and you should see an Edit option.

 

[SEASIA]

Wouldn't this work for you directly?

=IFERROR(FIND("P",'Stock Options'!D3,6),0)

Ok, finally I replaced this formula with mine. And with that I removed two reference cells which replaced the beginning with zeros. Looks cleaner now.