regex split text to columns

[kkiii]

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.

[GaneshBo · Accepted solution]

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.

Best,

Ganesh

[apathetichell]

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]

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]

Thank you Ganesh. It works. 

[kkiii]

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

[kkiii]

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