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

SEASIA

New Member
Joined
Jul 23, 2022
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
btw; It doesn't need to be this LOOKUP formula. It can be whatever works as long the result is correct. Thank you.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
Excel Formula:
=IFERROR(FIND("P",'Stock Options'!D3,6),0)
 
Upvote 0
Solution
Wouldn't this work for you directly?
Excel Formula:
=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
 
Upvote 0
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. :)
 
Upvote 0
Wouldn't this work for you directly?
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,782
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top