Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Messy Value Data in String Field

dmd
5 - Atom

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.001500000
15000001500000
$1.5M1500000
1.5 MM1500000
~1.5mm1500000
Up to $1.5 Million1500000

 

Thanks!

3 REPLIES 3
mpennington
11 - Bolide

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]', '')))

Messy.png

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
8 - Asteroid

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
5 - Atom

Incredible! Thanks @mpennington 

Labels