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 · Accepted solution]

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. 

[TSP]

Agree that @mpennington's approach is excellent.

@dmd are there any circumstances in which a cell containing 'm' would not represent millions?

If not then you've got your answer already.

[dmd]

Incredible! Thanks @mpennington