Cell contains specific text, return the next 3 characters

[Byzie]

Hi all,

I am trying to find a way where I can return a value within a cell based on specific text.

My cell contain the below data.

Dear Victorian Power Networks - Rural, SPEAR: S178918T Responsible Authority: Greater Customer City Council Application Type: Certification Property Address(es): WARATAH GULLY, SMASHTOWN VIC 3551 Plan Number: PS123456U Estate Name: XXXXX Estate Stage G2 Council Reference(s): DS/128/2020, SC/145/2020/7 Applicant Reference: 1702011 - G2 Number of lots: 23 Action Required: Referral response required This application has been referred to you under Section 8 of the Subdivision Act of 1988. Please select the link below to access this application and provide a response. Message sent by the SPEAR system.

What I want to do is, if the cell contains "Number of lots:, then I want the number that come after it.. In this case, 23.

Any thoughts on how I can do this?
The naming format is consistent in this case also, if that helps, but the number can range from 1-2000.

Thanks in advance.

 

[Phuoc]

Try this:

=LOOKUP(9999,--MID(SUBSTITUTE(A1," ","|"),SEARCH("Number of lots:",A1)+16,{1,2,3,4}))

 

[Byzie]

Thanks Phuoc, works for some, however in most others, its 0.83333 or similar when the actual number is say, 3??

 

[Phuoc]

Updated the formula in post #2.

 

[Byzie]

Thanks Phuoc, works for some, however in most others, its 0.83333 or similar when the actual number is say, 3??

Updated the formula in post #2.

THANK YOU SO MUCH!! WHAT A LEGEND!

 

[Phuoc]

You're welcome!