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!
Solved! Go to 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.
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.
Incredible! Thanks @mpennington