Split a string field that has no delimiter into several columns

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 :

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

Your help will be really much appreciated.

Thanks,

Lorenna

Datasets

Regex

Data Investigation

Accepted answers

BrandonB

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

parsing.yxmd

All comments

BrandonB

Hi @munnelor

parse pattern.png

parsing.yxmd

gtejoram

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.

FixedLength.zip

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)?

Dreinagel

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

Thank you for sharing.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703