Hi,
I am trying to extract the numbers only from a field of strings. Example below:
Project Total- $463.2MM. Approved
RT:$134.0MM but current pending review.
Approved total - $198,336,058, routed for PO.
Expected output:
463.2
134.0
198.3
Is there a way I can do this based on the variability of some numbers shown in MM, and some in the full value? Otherwise can someone help how I can just extract the value after the "$" sign? Any help welcome.
Solved! Go to Solution.
My formula was a bit different, but I got it to work as well. Probably should have wrapped this in a Tonumber, as @Qiu did.
IIF(REGEX_Match([Field1], '.*\$(\d+),(\d)\d+,.*'), REGEX_Replace([Field1], '.*\$(\d+),(\d)\d+,.*', '$1\.$2'), REGEX_Replace([Field1], ".*\$(\d+\.\d)MM.*", "$1"))
Edit: Couldn't help myself, here is the Tonumber formula:
TONUMBER(IIF(REGEX_Match([Field1], '.*\$(\d+),(\d)\d+,.*'), REGEX_Replace([Field1], '.*\$(\d+),(\d)\d+,.*', '$1\.$2'), REGEX_Replace([Field1], ".*\$(\d+\.\d)MM.*", "$1")))
Wow. Thank you so much @Qiu. This works!
@AlYap
Glad to help and thank you for the accept mark.