Formula to extract a specific line item name from different cell

[shahid5788]

Hi,

I have alot of random data. I am trying to find a formula that will extract the "PO" from my memo cells. A formula that will look into the cell and find that specific PO and extract to my other column? Can anyone please assist?

MEMO		What I want
Invoice - California - California University of: PF 547548 Sales Orders Billing - PO573		PO573
Invoice - AFLAAC (U.S. Air Force Academy): 200hdi, dariW2 PO201		PO201
Invoice - Oklahoma State University: 300drt, htfJW2, vertical orientation (w/tircero kemur top plate) PO630		PO630
Invoice - New York State University: PL 874541 Sales Orders Billing - PO847		PO847

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[UniMord]

2 assumptions:

1. The first character after PO is a digit. Otherwise, we run the risk of pulling Poland, Portland, and Pomona as PO numbers.
2. The PO number isn't always 3 digits long, as you have in your example. If that were so, we could just enter =RIGHT(A2, 5).

=MID(A2, AGGREGATE(14, 6, SEARCH("PO" & {1,2,3,4,5,6,7,8,9,0}, A2), 1), 255)