Search for keyword in cell and return the numbers following the keyword in adjacent cell

Hello,

I am working on an Excel 2010 worksheet organized as as follows:

A B
1 blah..blah..KEYWORD 11.1..blah..blah..
2 blah..blah..KEYWORD 0.1..blah..blah..
3 blah..blah..KEYWORD 100..blah..blah..
.
blah..blah = both text and numeric. There's a space between KEYWORD and number

I would like to be able to have the numbers (integers, or having 1 or 2 decimal places) following the KEYWORD returned in blank adjacent cells as shown below:
A B
1 blah..blah..KEYWORD 11.1..blah..blah.. 11.1
2 blah..blah..KEYWORD 0.1..blah..blah.. 0.1
3 blah..blah..KEYWORD 100..blah..blah.. 100

Thanks in advance for any help.
xu9j2

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please see attached sample BEFORE and AFTER worksheets. Thanks.

With your search word in D1, this works for your example:

=MID(A1,SEARCH(D$1,A1)+9,4)+0

Thanks. It worked for the majority of the records.There were a few records where the keyword was ignored, and an #N/A was returned. I can do this manually now.

I meant to write #VALUE! was returned, not #N/A.

The keyword wouldn't have been ignored. The more likely scenario was that

=MID(A1,SEARCH(D$1,A1)+9,4)

returned a non-numeric value. Adding 0 to a non-numeric value will return the #VALUE! error. If you would like for us to resolve this issue, please share a few examples of your data where the formula returned an error.

Otherwise, if you are happy with the provided solution, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Please see attachment.

Column C indicates whether the keyword ("capacity") appears in column A cells. If the keyword does not appear in column A, an #N/A is displayed (see cell C3).
Column B shows the numerical values returned by the formula. Column D shows the keyword.
In rows 3, 5, 6, 9, 10, and 11, the formula should have returned numerical values, because the keyword was present in column A cells and it immediately preceded the numerical values.
The formula worked for rows 2, 4, 8, 12, and 13.

"The formula worked for rows 2, 4, 8, 12, and 13." -- the formula also worked for row 1: keyword is absent in cell A1, therefore #VALUE! was returned in cell B1

Try this in B1:

=IFERROR(MID(A1,SEARCH("capacity",A1,1)+8,SEARCH("GPM/",A1,1)-SEARCH("capacity",A1,1)-8)+0,NA())

It worked. Thanks a bunch.

You're welcome. Thanks for the rep!