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

Question

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?

Thank you very much in advance.

kat · Accepted Answer

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!

Cheers

Kat

jdunkerley79 · Accepted Answer

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