Dynamically Create New Column for Each Batch Formula in Macro

[dmpope]

Hi all,

I'm trying to create a batch macro to create a new column for each substring formula used to parse out a text file. Regex and Text to Columns can't be used do to a line changing depending on account activity and would error out too often if the pre-defined regex or text to columns configurations don't match.

So basically I have the parameters for the formula in a Text Input because the position and size of the parameters don't change (just whether that info is there day to day is what changes, could be a set of spaces or a set of numbers/letters). I want to create a batch macro that will run through each record ID and update the substring formula and have each batch create a new column.

Example:

A10 20230321 1234 ASFHK1235

A20 0000021341 213               01     123

A3 1216887 ASD 12124909SAYU 00000000000               21421        12

and so on.

Is this something that can be done? I know I can update pieces of a formula with the batch macro, but I'm not sure how to create a new column with it each time (without it overwriting the column I add in the formula).

Thank you!

Edit to add example expected output:

A20 0000 21  341 2 13 (new column - needs to be dynamic for new incoming data) 01 (new column) 123

and so on. Really just looking to be able to add new columns to account for future incoming data that would be in those spaces.

[binuacs]

@dmpope would you be able to provide the expected output based on the above input, that will give more clarity on your requirement. 

[FinnCharlton]

Hi @dmpope,

If you have multiple formula parameters, and you want a new column for each, and the parameters do not change, could you simply write out all of the formulas, each in a new expression within a formula tool. This would make a new column for each set of parameters, without the need for a batch macro.

If there are too many sets of parameters, or you need them to be dynamic, check out the 'Dynamic Formula' CReW macro, which you can download here: http://www.chaosreignswithin.com/p/macros.html 

This macro allows you to add a list of formulae as a data input, and will let you specify the column name and expression to apply.

Hope this helps!

[dmpope]

@binuacs edited to add an example of the expected output. Thanks!