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?


[TABLE="width: 929"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]MEMO[/TD]
[TD][/TD]
[TD]What I want[/TD]
[/TR]
[TR]
[TD]Invoice - California - California University of: PF 547548 Sales Orders Billing - PO573[/TD]
[TD][/TD]
[TD]PO573[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice - AFLAAC (U.S. Air Force Academy): 200hdi, dariW2 PO201[/TD]
[TD][/TD]
[TD]PO201[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice - Oklahoma State University: 300drt, htfJW2, vertical orientation (w/tircero kemur top plate) PO630[/TD]
[TD][/TD]
[TD]PO630[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice - New York State University: PL 874541 Sales Orders Billing - PO847[/TD]
[TD][/TD]
[TD]PO847[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)