Excel Formula to extract previous word (towards left) from a specific position

Question

I have multiple records as below in an excel file say Col A:

• Infogain India (P) Ltd. 3-6 yrs Noida
• ROBOSPECIES TECHNOLOGIES PVT LTD 0-2 yrs New Delhi
• Red Lemon 0-3 yrs Noida(Sector-7 Noida)

Within the data there is a range of years mentioned e.g. 3-6 yrs in the first list item.

I want to extract the data 3-6, 0-2, 0-3 etc from above 3 list items. I understand a search for " yrs " in all the strings will give me the end position. However, I am unable to determine how to find the starting position of the Number of years.

I require the excel formula which will give me the year range. I do not want to use any VBA for the solution.

Answer 1

If there are no spaces between numbers then you can use following formula.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A3," yrs",REPT(" ",99)),99))," ",REPT(" ",99)),99))