Extract from text string with conditions

[Redzero1]

Hello All, 

 I have a request I'm working on and I'm a bit struggeling to get the information I want :

I have one column from Excel file caled "Comments" it a free text and Iit contains the Age (the information I want to extract)

the age is expressed {number}y ( 10y , 2y , 20y ....) and sometimes 10yr 

I would like to create a column to extract the age : the number before "y" or "yr"

tried to used Textbetween the issue is the "y" is also used in the string in other words and it's not a good delimiter .

can you please help to get this info ?

thanks 

[Redzero1]

sure here is an example :

Comment	Expected Result
minor results on patient1 3y after 5 iterations	3
result expected on 5yr patient after ....	5
lenght of treatment 6mos on alpha 18y following ...	18

basically need to parse the text and spot the numeric values before "y" or "yr"

[Ahmedx · Accepted solution]

pls try this

[Redzero1]

Thanks worked perfectly .

except where the age was not mentioned - I can figure it out 

thanks again

[Ahmedx]

you can share two or three lines so that we understand how to help you

[speedramps]

We want to help you but your description is too vaugue. 

Pleasae provide example input data as table text (not a screen print) so we can import the data to build a solution for you. Ensure you provide lots of different scenarios.
Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.

Remove any unneeded columns which may cause confussion.

Rename columns to user friendly names.
Remember not to share private data ... we don't want you to get into trouble. 😧
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions.
Remember you are gerrting free expert help, so please put lots of proper effort to asking questions and providing example.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming