Extract Multiple Types Text Strings

Hi

Working on a SAP report & need to filter down or find key contents a bit more than the report is giving

I can almost do this through Excel but having some issues getting the desired text extractions or results


For most columns, the following is (kind of) working but not to the full extent needed:
=IF(C22="","",IFERROR(TRIM(MID(SUBSTITUTE(C22," ",REPT(" ",99)),MAX(1,FIND("10",SUBSTITUTE(C22," ",REPT(" ",99)))+50),99)),""))

Requesting expertise & assistance with the attached file

Thanks

Hi Group

I know my example looks complex & there's definitely some complexity.

Think once I can get assistance with the 2 primary questions defined within example, I can manage the rest

Thanks for the assistance

Rather than describe what the formula should do, which is partially helpful, it's mich more important to manually add the ACTUAL results you expect to see. Only then can we be sure whatever we provide is correct.

At the moment we've no idea what you mean by 'kind of working...but not to the full extent.

When you add the results be sure to include an example for all relevant rows.

Hi Richard & Others

Good Point....

Revised file highlighting / commenting in line 16 of desired results seeking

New file name: Extract Desired Texts-v2

Before going too far with this have you asked whoever supplies the SAP file whether they can output a csv file instead?

Back in the day when I used SAP a lot we were always outputting csv files as the basis for reporting in Excel.

Hi Richard,

It would be fantastic if SAP could export into Excel or .csv file!!

It does for some reports but for whatever reasons, SAP Shortage reports don't export well

The day shown is the raw output into 1 column

That's why I'm seeking to extract the pertinent info. that's highlighted through the columns

Thanks

Originally Posted by mycon73

Hi Richard,

It would be fantastic if SAP could export into Excel or .csv file!!

It does for some reports but for whatever reasons, SAP Shortage reports don't export well

The day shown is the raw output into 1 column

That's why I'm seeking to extract the pertinent info. that's highlighted through the columns

Thanks

As I remember, and I'm going back 15 years or so, there are 'out of the box' standard reports that SAP churns out, typically .prn text type reports as you currently get, but users can define their own ad hoc reports and choose which filelds should be output from SAP Tables.

In short then, unless SAP has gone backwards over the last decade or so it almost certainly can output .csv files. You may need to find the teccy types who can help you grab them.

Richard,

Think I'm using an older version of SAP that's not Windows based or has less customize-able features like other SAP / ERP systems used

In the SAP version I'm using, some reports can be exported out nicely to Excel or .csv. For whatever reasons, the Shortage Report which for this company highlights items to be ordered, ordered & when they should be arrive. This particular report is not a straight export & the only I can get into Excel is the format given

Getting back to the matter at hand....

For majority of content or text strings, searching a parameter, such as "PO" (Purchase Order) & want the PO # along with other pertinent data needed as defined through columns

How to get these?

Hi,

Does the attached help

I've added formulae to row 37 and down a few rows on the Extract 2 sheet.

I couldn't find a SO# to test column I but no doubt you can adapt one of the other formulae to pick that up when it occurs.

Hi Richard,

Almost works!!

Given the data in column C or all in 1 column, definitely makes a bit difficult to "cleanly" extracted required info.

Sweeping the functions down or up, there are still some issues within the cells or rows

However, it took me a long while but did finally managed to take this Shortage Report & export it "cleanly" into Excel, which then I can now manipulate a bit easier!!

Thank you very much for trying -