Split text based on char type

[chillerbox]

Hello All,

I want to split this text:

from this:

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

Into this:

List 1	List 2	List 3	List 4
0580901	0.00	155,000.00	51,700,715.00
8980901	0.00	155,000.00	51,700,715.00

Please kindly suggest how can I achieve this? the only pattern is they start with 7 digits numbers.

Thank you before, any clue is appreciated.

[Agnius · Accepted solution]

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/

[chillerbox]

Hi Agnius,

Thank you for your response, it worked like wonder.  Do you happen to know how to get the text before the parsed one?

"BRAVIA10458056700005349ATM John Doe" -> this text does not have any pattern and the length is dynamic.

[Agnius · Accepted solution]

You could do that by modifying the regular expression pattern to .+\d{7}\s\d.+

Doing so will essentially include everything that comes before the 7-digit number all the way since the previous newline.

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

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[chillerbox]

Hi Agnius,

Again, thank you for the reply. Right now, I'm using crop text to match the text before parsed text, but I will test your solution to see which is faster.