Help with regular expressions

Hello Everyone,

I am trying to parse the pdf files and struck with splitting the row with dynamic columns. I need to split the row multiple columns as shown below.
split the TEXT to 8 columns

tried using the below formula but it’s not working

Much appreciated your help to resolve this issue

thanks

Regex

Accepted answers

flying008

Hi, @Skotha123456

FYI.

录制_2024_11_12_11_29_29_788.gif

All comments

CoG

Have you tried Text to Columns Tool with \t as the separator? This looks like it may provide the simplest solution.

Skotha123456

Hi ,

I have tried text to columns but it’s not working as expected

Thanks,

sree

binu_acs

@Skotha123456 can you upload a sample file? is it delimited with space or tab?

CoG

If it's delimited with multiple spaces, you can use the RegEx Tool, configured to Tokenize to 8 columns with the following expression:

(.*?)(?:\s{2,}|$)

This will capture all items that are delimited by 2 or more spaces.

If that doesn't work, then I'm with @binu_acs. Sample data would be helpful.

Hope this helps and Happy Solving!

apathetichell

Upload data not images. I don't knon where you got your formula but it's very wrong.

REGEX_Replace([YourField], "^(\\d+)\\s+([^\\d]+)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(.+)$", "$1|$2|$3|$4|$5|$6|$7|$8|$9") --- makes no sense for what you are trying to do. you want all 9 columns. your formula looks for 9 or statements. every single time the first time will create a TRUE so you will never look at Or statement 2.

try throwing this into the regex tool in parse mode change it by:

1) - replace your \\ with \ ---- \\ in perl/Alteryx regex - \\ would mean the character "\" --- so you do not want this. Don't trust gemini/chat gpt for this.

2) remove your | --- you don not need ors - you are trying to parse a single field for 9 distinct columns.

^(\\d+)\\s+([^\\d]+)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(\\d{1,3}(?:,\\d{3})*|-)\\s+(.+)$

follow those instructions and see what maps.

flying008

Hi, @Skotha123456

FYI.

录制_2024_11_12_11_29_29_788.gif

Skotha123456

Thanks everyone, @flying008 many thanks and i took your suggestions and working fine with a small change

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703