Seperating text with Regex

[gdorwart]

Hello all, 

I've been struggling to learn seperate a line of text using RegEx. Below is the current format in Excel. 

 

Here is the desired format after RegEx has been run:

Any help with this would be amazing!

[FinnCharlton]

Hi @gdorwart ,

I would try something like this using the regex tool:

[gdorwart]

In your RegEx expression, what is the function doing exactly? Thanks for the help!

[FinnCharlton]

Hi @gdorwart , it is finding every instance where there are more than two spaces in a row, and replacing it with a "|" character. We can then use Text to Columns to split on that character.

[BS_THE_ANALYST]

One way to do this. Attached the workflow as a file.

It's seperating the header column from the data below it. It's splitting the header column by the delimeter " " (a space). You can change this delimeter based on what the delimeter between your headers is. 

The data below it is being split using regex (.*)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s*$ this expression says that it split each record into 4 groups: it will firstly capture the name of the group i.e. accountancy 2021. Then it will proceed to capture the next 3 groups of digits i.e. 20.1   2021   0

[binu_acs]

@gdorwart One way of doing this