Solved: Split text based on char type

Split text based on char type

You need to use Parse text with regular expressions enabled and use the following pattern to extract the relevant values first: \d{7}\s\d.+

This will result in a list of matches with the following items:

0580901 0.00 155,000.00 51,700,715.00
8980901 0.00 155,000.00 51,700,715.00

You can then loop through the list and use Split text on each item to split it by space. And then you can add it to your new lists.

Here's a sample flow:

And here's a snippet that you can copy and paste to your PAD designer to have those actions generated automatically for you:

SET Text TO $'''BRAVIA10458056700005349ATM John Doe 0580901 0.00 155,000.00 51,700,715.00
BRAVIA10458056700005349ATM John Doe 8980901 0.00 155,000.00 51,700,715.00'''
Text.ParseText.RegexParse Text: Text TextToFind: $'''\\d{7}\\s\\d.+''' StartingPosition: 0 IgnoreCase: False Matches=> Matches
Variables.CreateNewDatatable InputTable: { ^['Column1', 'Column2', 'Column3', 'Column4'], [$'''''', $'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
LOOP FOREACH CurrentItem IN Matches
 Text.SplitText.Split Text: CurrentItem StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextList
 Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: TextList
END

Note: I use a data table there, because that's a personal preference. If you need those values in lists, you can also create 4 separate lists and then add the values separately to each of them inside the loop. You can reference the values of %TextList% after using Split text by using an index as follows:

Here's a snippet for this approach, too:

SET Text TO $'''BRAVIA10458056700005349ATM John Doe 0580901 0.00 155,000.00 51,700,715.00
BRAVIA10458056700005349ATM John Doe 8980901 0.00 155,000.00 51,700,715.00'''
Text.ParseText.RegexParse Text: Text TextToFind: $'''\\d{7}\\s\\d.+''' StartingPosition: 0 IgnoreCase: False Matches=> Matches
Variables.CreateNewList List=> List1
Variables.CreateNewList List=> List2
Variables.CreateNewList List=> List3
Variables.CreateNewList List=> List4
LOOP FOREACH CurrentItem IN Matches
 Text.SplitText.Split Text: CurrentItem StandardDelimiter: Text.StandardDelimiter.Space DelimiterTimes: 1 Result=> TextList
 Variables.AddItemToList Item: TextList[0] List: List1
 Variables.AddItemToList Item: TextList[1] List: List2
 Variables.AddItemToList Item: TextList[2] List: List3
 Variables.AddItemToList Item: TextList[3] List: List4
END

I personally prefer working with data tables, though, as it is cleaner and usually requires less actions.

Alternatively, you can create the data table originally (my first sample) and add 4x Retrieve data table column into list actions after the loop to save the columns as lists like this:

Here's the snippet for the 4 extra actions:

Variables.RetrieveDataTableColumnIntoList DataTable: DataTable ColumnNameOrIndex: 0 ColumnAsList=> List1
Variables.RetrieveDataTableColumnIntoList DataTable: DataTable ColumnNameOrIndex: 1 ColumnAsList=> List2
Variables.RetrieveDataTableColumnIntoList DataTable: DataTable ColumnNameOrIndex: 2 ColumnAsList=> List3
Variables.RetrieveDataTableColumnIntoList DataTable: DataTable ColumnNameOrIndex: 3 ColumnAsList=> List4

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

Quick Links

Leaderboard > Power Automate