Find date in the first paragraph of a cell having multiple paragraph

[debraj_patra1]

Hi in an excel sheet colomn have cell having multiple paragraph with Dates in it after a particular text " On"

i want to built a formula which will look for the specific text " On" and extract the date after it only for the first paragraph in the cell.

comments
"MAC Book Pro - 2,450/- Per Month. MacBook Pro 13-inch Retina dual-core i5 2.7GHz/8GB/256GB/Iris Graphics
This MacBook Pro 13-inch allocated to Abbas Zaidi (IN8807) as per the list provided by agency on 19-Sep-2017.

MAC Book Pro - 2,450/- Per Month. MacBook Pro 13-inch Retina dual-core i5 2.7GHz/8GB/256GB/Iris Graphics
This MacBook Pro 13-inch allocated to Sweta Chand (IN8807) as per the list provided by agency on 12-May-2017.

MAC Book Pro - 2,450/- Per Month. MacBook Pro 13-inch Retina dual-core i5 2.7GHz/8GB/256GB/Iris Graphics
This MacBook Pro 13-inch allocated to Manu Mehta (IN8807) as per the list provided by agency on 12-Apr-2017.

MAC Book Pro - 2,450/- Per Month. MacBook Pro 13-inch Retina dual-core i5 2.7GHz/8GB/256GB/Iris Graphics
This MacBook Pro 13-inch allocated to Rohit Singh (IN5849) as per the list provided by agency on 13-Oct-2016.

MAC Book Pro - 2,450/- Per Month. MacBook Pro 13-inch Retina dual-core i5 2.7GHz/8GB/256GB/Iris Graphics
This MacBook Pro 13-inch allocated to Amrit Arya (IN5849) as per the list provided by agency on 28th Aug 2015."

Regards
Debraj

 

[Rick Rothstein]

Assuming the first paragraph always has a date in it and that the word "on" only exists once in that first paragraph (in front of the date you want), then give this formula a try...

=LOOKUP(9.9E+307,--LEFT(MID(A1,SEARCH(" on ",A1)+4,99),ROW($1:$99)))

Note: This formula returns the serial date number so you will have to use Cell Formatting to make it look like you want.

 

[debraj_patra1]

Thanks for your reply,

now i am facing an issue in the cell having below contents, here data is not fetching

"MAC Book Pro - 2,450/- Per Month. MacBook Pro 13-inch Retina dual-core i5 2.7GHz/8GB/256GB/Iris Graphics


This MacBook Pro 13-inch allocated to Suhel Sayyed (IN8652) as per the list provided by agency on 31st Aug 2015."

Thanks in advance.