Split a string field that has no delimiter into several columns

[munnelor]

Hi 

New to Alteryx so I am not sure how to proceed to split a string fields that does not have delimiters into several columns. 

I was thinking if using regex but i cannot find the correct expression to split the following string : 

 
DADAUG5045075DADAUG5045075747000 AU13C95410300008158 DADAUG504

And it should be splitted this way :  

Column 1 | Column 2 | Columns 4 | Column 5 | Column 6 | Column 7                           | Column 8 

D              | AD           |  AUG50      | 45075      | 747000    |  AU13C95410300008158  | DADAUG504

Your help will be really much appreciated. 

Thanks, 

Lorenna 

[BrandonB · Accepted solution]

Hi @munnelor 

Your regular expression depends entirely on what pattern of characters that you are expecting. The below expression technically parses the data how you need, but only based on the number of characters. First column has one character, second column has 2, third has 5, etc. It would be helpful if you know the logic for how these fields should be split. Workflow is attached. 

[gtejoram]

Hi

Read the file as fixed width text file and import or browse the attached setting file, or else you can select how to split the data in top of the window.

[MarqueeCrew]

@munnelor,

You've got several good solutions already, but seeing that you haven't marked them solved yet I will provide another.  While I am a big fan of RegEx, I don't think that this challenge calls for pattern matching.  I do favor using a FLAT definition, but here's a simple set of formulas:

Left([Field1],1)
...
The rest are substrings:

Substring([Field1],1,2)
...
Substring([Field1],3,5)
...
etcetera.

The 1st numeric argument is the starting position (zero based).  So position #2 for a length of 2 actually becomes 1 for a length of 2.

Cheers,

Mark

[munnelor]

Thanks a lot for your help it works great. 

Instead of having an expression like this (.)(....)(.....)(........)(........)

Is there a possibility to enter the number of characters in the expression as (\d1)(\d4)(\d7)? 

[BrandonB]

Hi @munnelor 

There are other ways to write regular expressions depending on the pattern that your data has. In this case, the period signifies a character so having four periods would indicate four characters. The parenthesis defines a "group" which is going to be put into its own column. 

There are ways, for instance, to say that you want to capture all of the numbers up until you hit a letter, or all of the letters until you hit a number. Some people will use sites like regex101.com or others to learn regular expressions because it is not just an Alteryx exclusive functionality. 

To answer your question, yes, you can also use: 

(.{1})(.{2})(.{5})(.{5})(.{6})\s(.{19})(.{10})

[hamidansari]

any suggestions if the columns that i want to split has inconsistent length? Sometimes it can be 6 characters, sometimes 11. 

I essentially want to split in to four columns: 1, 5, 2, and 3 character length.  

however, when i use this forumula: (..)(.....)(..)(...). the rows with only 6 characters do not return anything into the new four columns created through parse. Any suggestions would be helpful. 

[Dreinagel]

Wow!  Thank you.  I had a similar issue and your solution was golden!  

Thank you for sharing.