A shorter article this time, showing new Excel stuff that is applied to code from a Turbo Integrator process. While updating a larger number of processes the other day, I needed to parse ExecuteProcess and RunProcess statements. For instance:

nRet = ExecuteProcess( 'TECH_create template for a cube',
    'pLogoutput', 0, 'pStrictErrorHandling', 0,
    'pOutputMode', 1,
    'pCube', 'OPEX_HR',
    'pView', '',
    'pFilePath', '1+', 'pFileName', 'Export_FTE_' | pCountry | '.txt', 'pFileAppend', 0,
    'pSuppressZero', 0, 'pSuppressConsol', 0, 'pSuppressRules', 0, 'pSuppressConsolStrings', 0,
    'pDelim', '009', 'pQuote', '',
    'pDecimalSeparator', '.', 'pThousandSeparator', '',
    'pTitleRecord', 'Actuality|Value Type|Currency Type|Measure|Company|Department|Account|Period',
    'pIndexNrColumnName', '',
    'pCubeNameColumnName', '',
    'pValuesColumn', 2,
    'pReplaceCharacterForSpaceInTitle', '_',
    'pTemp', 1, 'pSandbox', '', 'pCharacterSet', '',
    'pMDXExpr', '',
    'pOutputDimensionOrder', '4|7|3|8|1|2|6|5',
    'pSelection_Filter', 'Currency_Type¦LC & Actuality¦' | pVersion | ' & OPEX_HR_Account¦FTE',
    'pSelection_SubsetNames', '|||||||' | cSubset_Periods,
    'pSelection_SubsetNameDefault', cSubset_Name,
    'pSelection_NumberOfElements', 0,
    'pSelection_Reference', '6¦P + A_Desc',
    'pDimension_In_Columns', '2|Total_Year',
    'pListDelim', '|', 'pDimDelim', '&', 'pEleStartDelim', '¦', 'pEleDelim', '+',
    'pCellFormatting', '', 'pWrite_Code_For_Load_Process', 0,
    'pKeep_Record_Condition_01', 'cu:1=[Department_Validation].(1|2|Valid combination)',
    'pKeep_Record_Condition_02', '2d:6=EL(FTE)|7=EL(Basis)' );

A line can contain several parameters. I needed to parse the different pairs of parameter name/parameter value. This would give me:

The following Excel dynamic arrays formula can do that:

=SUBSTITUTE( WRAPROWS( TRIM( DROP( TEXTSPLIT( TEXTJOIN( "", FALSE, A1:A27 ), "," ), , 1 )), 2 ), "'", "" )

TEXTJOIN glues the different lines in the function call together.

TEXTSPLIT splits the long string on the ,  [We know that this is not 100% correct in case extra columns appear inside a parameter value]

DROP removes the first column the split result. (ExecuteProcess('process name')

TRIM removes excessive spaces

WRAPROWS creates the name/value pairs: 20 columns becomes 10 rows of  columns each.

SUBSTITUTE removes the single quotation marks.

It's fair to say that modern Excel formulas are totally a whole new world compared to legacy Excel fomulas.

I also tried the REGEXEXTRACT function here but it proved not that easy for the regex, and Excel's implementation of REGEX is not the most extensive either (lookahead, lookbehind patterns).

WRAPROWS, WRAPCOLS, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, EXPAND, VSTACK, HSTACK, ...

Plenty of good new stuff, together with LAMBDA, LET, SCAN, REDUCE, REGEX functions, and countless others. Definitely worth experimenting here.