Extracting numbers from a string where they follow the letters "ps"

I need to extract any numbers (0-9 only no symbols) that follow the letters "ps".
"ps" could be in either upper or lower case or a mix of both. There may also be some hyphens or brackets between the ps and the numbers but no text. I need to extract just the numbers and only where there is no other text between the ps and the numbers. The ps could be anywhere in the text string and will not always be at the end of the string (although this will be the most common)
Eg.
C1464 PS25 - I want to extract the 25
SUS680 H.PUMPS - I do not want to extract anything
C1496 PS3 - I want to extract the 3
U57119-p/s 12 - I want to extract the 12
U57119 ps(13) - I want to extract the 13

I assume that I would use Search to find the letter ps but I am unsure how to extract the numbers after that where there may be spaces or symbols and where the numbers may be 1 or 2 characters long.

Thanks

Originally Posted by nflsales

=iferror(abs(trim(right(substitute(upper(a1),"S",rept(" ",len(a1))),len(a1)))*1),"")

Hi Silva,
Thank you for that. The only problem is where the cell only contains a number and no text. It is then giving me the number as the result when in fact I dont want anything if it is not preceeded with the PS text. Are you able to help with that?
Thanks again

Originally Posted by nflsales

=iferror(if(isnumber(a1),"",abs(trim(right(substitute(upper(a1),"S",rept(" ",len(a1))),len(a1)))*1)),"")

Hi Silva,
Thank you for the new version, however this is still not working for me. I need the letter PS not just an S but if I change your "S" to "PS" and the a1's to B9, then I get no results at all even when I should.
Sorry, not sure if I am being a bit thick here.

Hi Silva,
When I pasted in your formula a second time it worked except for a few records, but those particular records have extra letters between the ps and the numbers or have hyphens or slashes in them or in the ps e.g p/s. My managers have now decided that we will use the formula you have provided and do the few that don't work manually. Thank you for your help.

Try this UDF:

Please Login or Register  to view this content.

Or this ...

Please Login or Register  to view this content.