Extract data after "-" VBA

[Liam4567]

**VBA**

Hi

I have a string that will change daily and looks something like this "09876 k-wef234 £45678.99 Option 4 -£123456.57"

I am trying to find the best way of extracting the "-£123456.57" after "Option 4 -£123456.57". I need the code to always look for Option 4 as that is the one part that will always stay the same, and the number after will always start with a minus. It needs to look for Option 4 as it is going to loop through multiple rows of data looking for the words Option 4.

I will then be using that figure (-£123456,57) to be the value of a cell on another sheet.

Thanks in advance.

 

[Liam4567]

So there will only be one cell in column A containing "Option 4"?

Yes correct

There will be lots or rows containing data, and some may say Option 5, but only one will contain 'Option 4' and then I just need the figure after that into cell D2 on sheet2, and that figure will always start with a -

 

[Peter_SSs]

I edited my previous post. Does the edit answer your question?

 

[Liam4567]

So there will only be one cell in column A containing "Option 4"?

Edit:
If so, do you really need vba for this or would a formula like this do?

=-RIGHT(SUBSTITUTE(VLOOKUP("*Option 4*",PDF!A1:A1000,1,0),"£",REPT(" ",20)),20)

That worked, thanks for your help

 

[Peter_SSs]

You're welcome. Glad we got there in the end.