Messy Value Data in String Field

dmd

Hi All, 

I have rather messy value data in a v_string field that I am needing to convert into a numeric field for calculations. I have listed some examples below. I know I will likely need a series of formula or regex tools but am struggling to find a series of solutions that work for each of the "problems" collectively.

Original Data (string)	Target (numeric)
1,500,000.00	1500000
1500000	1500000
$1.5M	1500000
1.5 MM	1500000
~1.5mm	1500000
Up to $1.5 Million	1500000

Thanks!

mpennington

This formula should do the trick for you, although you will need to build on it, if your data evolves:

IIF(REGEX_Match([Original Data (string)], '.*m.*'),
Tonumber(REGEX_Replace([Original Data (string)], '[^\.\d]', ''))*1000000,Tonumber(REGEX_Replace([Original Data (string)], '[^\.\d]', '')))

I'm just looking for a match of 'm' with anything to designate millions and then multiplying a clean number times that value if it has the designation.