Use one Regex tool to split String with various spaces

[Alexander_Gruber_FhA]

Dear all,

i would like to split a string according the following table. The required informations are seperated with a different amount of spaces. Within the arcticle describtion (target column number 3) the amount of words can be differ also from row to row.

Inital situation:

\par 310035 272898 !!CL UNGARISCHE SALA 5 2 Wurst 082010 10-NOV-20 698862 06:59:22 07:14:08 07:00:15

Target:

310035

272898

!!CL UNGARISCHE SALA

5

2

Wurst

82010

10-Nov-20

698862

06:59:22

07:14:08

07:00:15

Is it possible just to split this string with one regex tool, or do I need more tools? I would appreciate your support.

Thanks in advance,

Alex

[PhilipMannering]

For REGEX,

Tokenize on,

\S+

Then use a formula Tool to concatenate for !!CL UNGARISCHE SALA

To do it in one go,

(!![\s\u]+|\S+)\s

For Text To Columns,

Use a space. 

[estherb47]

Hi @Alexander_Gruber_FhA 

If it's separating purely on spaces, then @PhilipMannering 's solution is perfect.

If it's not, then you can look to separate by what defines each grouping.

e.g., with 
RegEx parse, I would use this pattern
:

(\d+?)\s+(\d+?)\s+(\D+?)\s+(\d+?)\s+(\d+?)\s+ should separate up until the "2" before the 
Wurst"

Then easiest to use another regex on the rest

I've found that too many columns in a RegEx parse fails to work, so my experience has been to divide it up a bit.

Let me know if that helps

I'm basically separating on numbers, non-numbers, and spaces in between those groupings.

Cheers!

Esther

[atcodedog05]

Hi @Alexander_Gruber_FhA 

Here is the regex for the task. Use in parse tool

.*\s(\d+)\s(\d+)\s(.*)\s(\d)\s(\d)\s(\w*)\s(.*)\s(\d+)\s(.*)\s(.*)\s(.*)

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Alexander_Gruber_FhA]

Dear all,

thank you for your quick response.

I just saw that my table was slightly modified when the post was sent. The table actually looks like this (● means space):

\par●003135●●●882279●!!CL●UNGARISCHE●SALA●●●●●●●●●●5●2●●●Wurst●●●●●●082010●10-NOV-20●●●●●●●●●●●●698862●●●●●06:59:22●07:14:08●07:00:15

Another row can look like this for example: Information for target column 3 differs.

\par●003135●●●882279●UNGARISCHE●SALA●ex●●●●●●●●●●5●2●●●Wurst●●●●●●082010●10-NOV-20●●●●●●●●●●●●698862●●●●●06:59:22●07:14:08●07:00:15

Please excuse this addition.

Thanks,

Alex

[atcodedog05 · Accepted solution]

Hi @Alexander_Gruber_FhA 

Here is the modified workflow

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[PhilipMannering]

I think my solution would still work (first post)

[Alexander_Gruber_FhA]

It works. Thank you very much!

[atcodedog05]

Happy to help 🙂 @Alexander_Gruber_FhA 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any other issues 🙂

[Alexander_Gruber_FhA]

Hi @atcodedog05,

unfortunately I have to ask you again within yesterday´s topic.

There are still two problems.

1.)

Initual situation:

\par●003135●●●882279●!!CL●UNGARISCHE●SALA●●●●●●●●●●5●2●●●Wurst●●●●●●082010●10-NOV-20●●●●●●●●●●●●698862●●●●●06:59:22●07:14:08●07:00:15

After Regex:

003135

882279

!!CL●UNGARISCHE●SALA

5

2

Wurst

082010●10-NOV-20

698862

06:59:22

07:14:08

07:00:15

Column nr. 7 do not split the number and the date.

2.)
The code did not work for rows like this:

\par●003135●●●892440●Ja! Example KG*●●●●●●●●●●1●1●●●Obst - FD●●●●●●082010●10-NOV-20●●●●●●●●●●●●698862●●●●●06:59:22●07:14:08●07:00:15

After regex:

003135

892440

Ja! Example KG*

1

1

Obst

FD●082010●10-NOV-20

698862

06:59:22

07:14:08

07:00:15

Column nr. 7 integrates FD. But Column Nr. 6 should look lke this "Obst - FD".

Ident to the first problem, the number and the date in column Nr. 7 should be splitted.

Please excuse the additional question. I´m relatively new to regex.

Thank you very much for your support!

Alex