PowerPivot - Data Analysis Expression

**jantonio** · 06-01-2012, 01:49 PM

Hello Everyone

Need your assistance on the following scenario for extracting a PO#. I got my formula working but believe it can further improved.

This is how data is populated under the "Shipper_Description" column field:
SLAC _ 24 PACKAGES OF OUTD OOR PLASTIC PRODUCTS HS CODE: 9403 PO: 9400173962

My formula under "PO":
=RIGHT([Shipper_Description],LEN([Shipper_Description])-SEARCH(" PO: ",[Shipper_Description],1))

Result: "PO: 9400173962"

1st question: How can I just simply get "9400173962" without including the PO text? I know it can be done instead of doing =mid([po],4,11).

Last question relevant to topic.

Sometimes, I have multiple PO's under the same line but my formula only captures the 1st one.
Example: PO: 4300175985 _ 4300175713

How can I capture both separated by a comma delimiter using the same formula?
=RIGHT([Shipper_Description],LEN([Shipper_Description])-SEARCH(" PO: ",[Shipper_Description],1))

Thank you.
Antonio

**oeldere** · 06-02-2012, 05:17 AM

If there are always 10 characters, you can use the formula in my workbook.

Do you have questions, just ask.

**jantonio** · 06-05-2012, 10:57 AM

Thank you for your input... but I can't use regular excel formulas for this purpose or else I would require multiple columns.

I was able to work out this last week with my updated formula.

My formula under "PO":
=RIGHT([Shipper_Description],LEN([Shipper_Description])-SEARCH(" PO: ",[Shipper_Description],1)-4)
Result: 9400173962

**oeldere** · 06-05-2012, 11:23 AM

Thank you for your input... but I can't use regular excel formulas for this purpose or else I would require multiple columns. So?

Does it always have 10 characters?

And how is your solution when you getting multiple PO's ?