community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Other option than using multiple "Text to Column" for multiple columns

Meteor

Hi all expert, i have a dataset with about 12 columns having the format shown below.

I am trying to extract 5th numbers from all the columns. for example: ~

[0,3,0,0,0,28,0,0,0,2] --------------------> 28

[0,0,0,0,0,3457,407,605,10,7837] ----> 3457

Anyone has any idea on how to achieve this?

image.png

Thank you very much in advance.

 

 

 

 

Quasar

Hi @ElwinPang

 

Here is a solution that should work for you. It's using Regex & a multi-field formula. This means you only need one tool to get it right!

 

regex_multifield.PNG

 

Cheers

Kat

I agree with @kat approach, suggest just a slight simplification to the Regex:

 

REGEX_Replace([_CurrentField_], '\[(.*?,){4}(\d+),.*\]', '$2')

 

Just a bit simpler if want different columns (change the 4 to 1 minus the column you want). Please note if picking last column would need to amend further to say:

REGEX_Replace([_CurrentField_], '\[(.*?,){4}(\d+)[,\]].*', '$2')
Labels