How to convert % percentage sign to number in a simple way

[charmrain]

Hi, 

i want to find out a simple way to translate the numeric data with the "%" percentage sign to a recognizable format, is there any simple way to do that?

please note, the variable blends with normal format value like 1.23 and "%" format value like 45.6%

Thanks

[MarqueeCrew]

Trim([field],'%')

That's pretty much all you would do to a string.

[SeanAdams]

Hey @charmrain

Firstly - let me thank you for posting an example flow with mocked up data - makes getting you to an exact solution much much easier :-)

I've added 2 pieces to your workflow.   The first one is wrong and doesn't work but is interesting because of the way it works, and the second works.

First version:

- Uses a data cleanse to strip punctuation, and then an auto-field to change the type

- This looks like the easy way to do this - but the bear-trap here is that the decimal place is also punctuation, so all of your numbers get turned into integers

Second Version:

- this uses a formula to do a simple replace of % with blank

- then does a data cleanse to strip whitespace, and auto-field to change the type

Second version does what you need.

If you're still curious - there's a great video by @JoeM in the live training weekly (here: https://community.alteryx.com/t5/Live-Training/Live-Training-Using-the-Data-Cleansing-Macro/td-p/438...) that will tell you how to adapt the Data Cleanse tool to give you the option to just strip off  % signs, in case this is a very frequent need of yours.

Hope this helps - if so could you mark it as solved - and if not, feel free to come back with questions or updates on the workflow.

Cheers

Sean

[charmrain]

Thanks for the quick reply.

i think there's a big problem with "Trim([field],"%")"

it will make 43.2% to 43.2 instead of 0.432. 

how could convert 43.2% to 0.432?

Thanks very much!

[MarqueeCrew · Accepted solution]

ToNumber(trim(Field,"%"))/100

How about that? You can put that to a double data type or use select to modify the type.

[SeanAdams · Accepted solution]

Got it - so if I understand correctly you want to :

- Treat things with % as a percentage, and then convert them back to fractions of 1 (e.g. 50% becomes 0.5)

- treat things without % as if they are already as fractions of 1?

Easy way to do this would be:

- Add a filter tool

- use the "Contains" and use %

- This way you get 2 streams of data - the ones with the % sign, and the ones without

- For the ones with the percentage sign - do exactly what we've done above (replace the % with blank, cleanse, fix type) - and then add in a formula tool to divide by 100

- for the ones without the percentage sign - just do the cleanse an fix type

- then use a union tool to stick both streams back together.

I've attached an updated workflow to demonstrate.

[charmrain · Accepted solution]

Hi Sean and  MarqueeCrew

thanks all of your kind help.

a new function marked as "simple solution" has been updated, i believe it's simpler for solving the mock case  :) 

Regards