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

Alteryx designer Discussions

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

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

Asteroid

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