Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

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

Highlighted
8 - 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.

 

 

 

 

Highlighted
12 - 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

Highlighted
16 - Nebula
16 - Nebula

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