We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Split a string field that has no delimiter into several columns

munnelor
5 - Atom

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 

7 REPLIES 7
BrandonB
Alteryx
Alteryx

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. 

 

parse pattern.png

gtejoram
5 - Atom

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.

 

Capture1.JPGCapture.JPG

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
munnelor
5 - Atom

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
Alteryx
Alteryx

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
6 - Meteoroid

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. 

 

hamidansari_0-1582039858996.png

 

Dreinagel
6 - Meteoroid

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

 

Thank you for sharing.

 

 

Labels