We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
fireworks
SOLVED

regex split text to columns

kkiii
6 - Meteoroid

Hi, 

 

I want to split a text box into two columns: the text name and the numerical values. The numerical value may have special signs like $ or (. 

$ sign can be removed. 

 

  • Residential mortgages $ 680 $ 418 $ (79) $ (135) $ 884
  • Personal loans 2,065 2,632 (1,381) (161) 3,155

How do I configure the Regex tool? Thanks in advance.

6 REPLIES 6
GaneshBo
Alteryx
Alteryx

Hi @kkiii 

 

How about this regex: ([a-zA-Z\s]+)([\W*\s\d]+) ?

The first marked group ([a-zA-z\s]+) means that it should include all alphabets, "a-zA-z" and spaces, "\s".

The second group ([\W*\s\d]+) means it should contain non-word characters, "\W", spaces, "\s" and digits, "\d".

 

Hope this helps. Please see attached sample.

GaneshBo_0-1624510787544.png

 

Best,

Ganesh

apathetichell
20 - Arcturus

Did you mean a text box or a text field? If it's a text box - feed that into a text input via an action tool and run it through this workflow - otherwise... I broke out the numbers too in part of my workflow... note datacleanse does the heavy lifting here - not regex.

 

 

DawnDuong
13 - Pulsar
13 - Pulsar

Hi @kkiii 

Firstly, it looks fo me like your data is from some accounting software export for formatted excel. Because the format of using (1.234) is used to denote negative number.

I suggest that you first check if there should be a space between $ and the numeric value. How to handle with vs. without that space is different.

if there is no space (that is $1,234 instead of $ 1,234), using a text to column tool (to split into different values) followed by a data cleanse / formula (to trim $ sign) will do the magic... you may need to do some processing if you want convert the (1,234) to -1,234

 

kkiii
6 - Meteoroid

Thank you Ganesh. It works. 

kkiii
6 - Meteoroid

Thank you for your reply. It's a text field from PDF input. The data cleaning has been used in the previous steps. 

kkiii
6 - Meteoroid

Thank you. It's the PDF input from an annual report. Data cleansing has been used in the process. 

Labels
Top Solution Authors