FInd negative decimals using Regex tool

[mohdhafiz]

Hi everyone,

Currently I am suing the Regex tool to parse data to their specific columns. However, the parsed data are not accurate where the -ve symbol of a value was separated to other column. Is there any improvement that can be made on my expression below?

This are the expression that I used:

(.*?)\s(.*?)(\d+\.\d+|n\/a?).*?(\d+\.\d+|n\/a).*?(\d+\.\d+|n\/a)\s(.*?)\s(.*?)(\d+\.\d+|n\/a?).*?(\d+\.\d+|n\/a).*?(\d+\.\d+|n\/a)

This are the input:

1 Finland 1.38 100.00 1.00 74 Malawi –0.07 64.39 0.48

47 South Africa (2007) 36.76 40.01 0.56 n/a Iceland n/a n/a n/a

n/a United Kingdom n/a n/a n/a n/a Kyrgyzstan n/a n/a n/a–1.05 40.44 0.15

This are the output errors encountered:

[lmorrell · Accepted solution]

Hi @mohdhafiz 

Workflow is attached.

If the field you're capturing has the potential to have a negative sign in front of it, then one solution is to put a [^\w]? at the beginning of the expression capturing numeric values. In this case, the updated RegEx will look like

(.*?)\s(.*?)\s(\d+\.\d+|n\/a?).*?(\d+\.\d+|n\/a).*?(\d+\.\d+|n\/a)\s(.*?)\s(.*?)\s([^\w]?\d+\.\d+|n\/a?).*?(\d+\.\d+|n\/a).*?(\d+\.\d+|n\/a)

Hope this helps! 

[mohdhafiz]

It worked. Thank you 🙂

[Muhummad_Solehudin · Accepted solution]

(.*?)\s(.*?)\s(\d+\.\d+|n\/a?).*?(\d+\.\d+|n\/a).*?(\d+\.\d+|n\/a)\s(.*?)\s(.*?)\s([^\w]?\d+\.\d+|n\/a?).*?(\d+\.\d+|n\/a).*?(\d+\.\d+|n\/a)

try use this

[mohdhafiz]

It worked, thank you.