Extracting numbers before a certain word or symbol in a long string of Text.

[pjaywon]

Hi. I am looking to extract several numbers from a string of text like the following:

"For the period beginning January 1, 2018, Supplier's fees for Services may be increased annually effective each January 1 upon 60 days prior notice to Customer. Any increase shall be based on actual increases in Supplier's costs, will not include mark-up and the increase shall be limited to the increase in the U.S. Department of Labor, Consumer Price Index for All Urban Consumers ("CPI") for the most recently available 12-month period preceding such 60-day notice period, or 2.0%, whichever is less."

In the above paragraph, I'd like to extract the "60" before "days" and the "2.0" before the "%" symbol into separate cells.

Can someone help me with this? I have over 12,000 cells of text that are similar, but not exactly like this paragraph and I'd like to get this information isolated into those cells.

Thanks!!!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any help. It assumes that the original cell(s) all do contain the text " days" and "%" & they are immediately after the relevant numbers.
I have provided two options for the 5. If you want the result as a number use the C2 formula (you could format the column for decimal places if you want) but if you want the exact format "2.0" then use the D2 formula but note that result is a text value, not numerical.

Excel Workbook
	A	B	C	D
1		Days	%
2	For the period beginning January 1, 2018, Supplier's fees for Services may be increased annually effective each January 1 upon 60 days prior notice to Customer. Any increase shall be based on actual increases in Supplier's costs, will not include mark-up and the increase shall be limited to the increase in the U.S. Department of Labor, Consumer Price Index for All Urban Consumers ("CPI") for the most recently available 12-month period preceding such 60-day notice period, or 2.0%, whichever is less.	60	2	2.0
Extract Numbers

 

[Phuoc]

Or try:

=-LOOKUP(1,-MID(A2,SEARCH(" days",A2)-{1,2,3,4,5,6},{1,2,3,4,5,6}))

 

[Peter_SSs]

Or try:

=-LOOKUP(1,-MID(A2,SEARCH(" days",A2)-{1,2,3,4,5,6},{1,2,3,4,5,6}))

Probably unlikely with the OP's data, but that 'could' return an incorrect result
"If I may 2 days will be set aside"

 

[Phuoc]

Probably unlikely with the OP's data, but that 'could' return an incorrect result
"If I may 2 days will be set aside"

Haha, you're right.