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.
How do I configure the Regex tool? Thanks in advance.
Solved! Go to 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
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
Thank you Ganesh. It works.
Thank you for your reply. It's a text field from PDF input. The data cleaning has been used in the previous steps.
Thank you. It's the PDF input from an annual report. Data cleansing has been used in the process.